SQL where Key = "Test" and ID = max(ID)-CodePudding

How do I select a row with a key where ID equals to the max ID. Since ID is automatically generated as a unique primary key.

SELECT * FROM TABLE WHERE Key = "Test" AND WHERE ID = max(ID)

I've tried, but it executes but returns blank.

SELECT * FROM Table WHERE Key= "Test" AND ID=(SELECT max(ID) FROM Table);

CodePudding user response：

your query is missing 'WHERE' clause in the inner query it should look something like this

SELECT * FROM [TableName] WHERE ID=(SELECT max(ID) FROM [TableName] WHERE Key= "Test");

there is another way mentioned on another similar question

SELECT TOP 1 * FROM [TABLENAME] WHERE Key="Test" ORDER BY ID DESC

CodePudding user response：

Very probably, the newest row - the one that got the highest id so far, has a value for key other than 'Test'.

So you can only get the row - among the rows whose key is equal to 'Test' - with the highest id .

Therefore, try:

SELECT * FROM table 
WHERE key='Test'
  AND id=(SELECT MAX(id) FROM table WHERE key='Test')