I need to select and Get a single unique value from a table where the key has duplicate entries. For instance there is a Transaction ID(Id) and an AccountNumber(AccountNumber) and for each account number there are many entries but I have to get only the Maximum (Transaction ID)(Id) for a particular AccountNumber. I am usnig the following code but gives me multiple rows in the result. Am I doing it right? There is no syntax error in this code . If I remove GROUP BY it gives an error stating no Group BY Statement.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
con.Open();
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter("select MAX(Id), Address, AccountNumber, Date, CustomerName, Debit, Credit, Balance from fianlTable WHERE (AccountNumber='"+textBox4.Text+"') ) GROUP BY Id, Address, AccountNumber, Date, CustomerName, Debit, Credit, Balance", con);
sda.Fill(dt);
dataGridView1.DataSource = dt;
Not specific to C# but more SQL based, you can select the records for the Account Number - sorted by TransactionId Desc and Limit the results to 1.
This would look like:
SELECT Id, Address, AccountNumber, Date, CustomerName, Debit, Credit, Balance from fianlTable WHERE (AccountNumber='"+textBox4.Text+"') ORDER BY Id DESC LIMIT 1
BTW Lucas is correct you are risking someone hijacking your code by using the value from the textbox directly in the query :)