Unique items (SQL)

Question

Hello everyone!
I have a table with a lot of items that look like this:
https://code.sololearn.com/cSXp357201mJ
What I want to do is to select all the items with a unique number and with the most recent timestamp. I tried distinct and group by, but that didn't work in a way I wanted it to. Any solution?
Right now, this is the query that I'm using: SELECT DISTINCT * FROM database WHERE TranspID=2 ORDER BY timestamp DESC
How do I keep only the most recent timestamp items with a unique number?

Answer 1

Depends on the db system you are using.
mssql:
SELECT TOP num
/* ... */
MySQL:
/* ... */
LIMIT num
Oracle:
/* ... */
FETCH FIRST num ROWS ONLY
/* ... */ -> rest of the query
num -> number of elements you want.
Select the most recent timestamp in the where part of your query to filter the results by this timestamp
MySQL example:
SELECT *
FROM your_table
WHERE timestamp = (
SELECT DISTINCT timestamp
FROM your_table
ORDER BY timestamp DESC
LIMIT 1
);