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

Question

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

You are almost correct, you forgotten inner query condition: `SELECT * FROM Table WHERE ID=(SELECT max(ID) FROM Table Where Key= "Test");` — Mani, Nov 25 '21 at 14:39
If you move the `WHERE` clause to the subquery, you'll get better results — HoneyBadger, Nov 25 '21 at 14:40
Works now after I've added Where Key= "Test" to the subquery. Thank you both Mani and HoneyBadger. — DamnSpaceship, Nov 25 '21 at 14:45
@DamnSpaceship That is a different question and should be asked separately, but the short answer is no. You have to list all columns, or use `*`, there is no *everything, but* construct in SQL (speaking about column list here). In general, using `*` is considered as bad practice (Except when used in `COUNT(*)` - counting rows or in `EXISTS (SELECT *...)` - checking if row exists). List the columns you need in the SELECT — Pred, Nov 25 '21 at 14:52
It's hard to say exactly what you mean by `MAX(id)`. Is that the max on the whole table, or just of the rows where `Key = 'Test'`? — Charlieface, Nov 25 '21 at 21:06

SomebodyAwful · Answer 1 · 2021-11-25T14:47:58.177

2

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

edited Nov 25 '21 at 14:47

answered Nov 25 '21 at 14:42

SomebodyAwful

score 1 · Answer 2 · answered Nov 25 '21 at 14:44

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

2 Answers2