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