How to select MAX (Id) max from the table Where key = some_value in SQL C #?

advertisements

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 :)