Muhammad Shahid Muhammad Shahid - 5 months ago 85
SQL Question

How to Select MAX(Id) max from Table Where key = some_value in SQL C#?

I have 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 an 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;

Answer

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