SQL SELECT DISTINCT ID from copies ORDER BY CID

Question

Hi everyone, i want something like this:

SELECT DISTINCT ID from copies WHERE timestamp < 1229444506 ORDER BY CID

the problem is that this one only return ID and i need CID and other columns in the table.

It may be that this is totally wrong for other reason aswell, so i will explain what i need.

I have a table that "record" every row-change in my maintable. This so in the future i will be able to go back in time and see how the main table looked like a certain date in time.

So what i need is a query that ORDER all rows BY CID, WHERE timestamp < 1229444506, AND then DISCTINCT them by ID.

I want to the query to return the first row by ID, when ordered by CID newest first.

Ive tried to get this working with different methods but no luck. Any suggestions?

Answer 1

SELECT * FROM copies WHERE timestamp < 1229444506 GROUP BY ID ORDER BY CID DESC;

EDIT:

SELECT * FROM copies WHERE timestamp < 1229444506 GROUP BY ID ORDER BY CID;

You can also try:

SELECT * FROM copies WHERE timestamp < 1229444506 GROUP BY ID ASC ORDER BY CID;

Or:

SELECT * FROM copies WHERE timestamp < 1229444506 GROUP BY ID DESC ORDER BY CID;

Does it work?

Answer 2

how can your database know which rows to append to your distinct column? say you haveː

 id | name
 ---+-----
  1 | a
  1 | b

now the hypothetical query: SELECT DISTINCT id, name FROM table. which name do you expect to show up in your result? a or b?

if that's not a problem you can group your result in mysql by id, but it’s unspecified which name you'll get

SELECT `id`, `name` FROM `copies` WHERE `timestamp` > 123456789 GROUP BY `id` ORDER BY `cid` DESC

Answer 3

I am not sure if I got it correctly, but what about creating a subquery that will just select the columns? You wrote: "I want to the query to return the first row by ID, when ordered by CID newest first."

So, let's make a subquery:

SELECT id, max(cid) as maxcid FROM copies WHERE timestamp < XX group by id

This will give you the relationship id <=> the CID you want. And now join it:

SELECT copies.* FROM copies, (SELECT id, max(cid) as maxcid FROM copies WHERE timestamp < xxx group by id) x  
WHERE copies.id=x.id AND copies.cid=x.maxcid;