CODEWITHSUNDEEP
mysqlsorting
Ommadawn
Ommadawn

Reputation: 2730

Order mysql data if I just have id's

I've got an intermediate table called Likes that is composed by the ID of a user and the ID of a picture, forming a composed primary key as it follows:

> show columns from Likes;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id_user | int(10) unsigned | NO   | PRI | NULL    |       |
| id_pic  | int(10) unsigned | NO   | PRI | NULL    |       |
+---------+------------------+------+-----+---------+-------+

My question is... if I select the id of a certain user, is any possibility of order his id_pic's by the moment they where inserted?

I mean, if I insert the values (id_user,id_pic): 

1,1
1,3
1,6
1,2

How could I obtain the values of id_pic for the user 1 in reverse order as they were inserted? (If it's possible..)

Thanks!

EDIT: I know that if I have an id for the table I could order by that id, but i'm NOT allowed to change the structure of the table :(

Upvotes: 1

Views: 28

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following using ROW_NUMBER and an ORDER BY:

SELECT *
FROM likes 
ORDER BY id_user ASC, ROW_NUMBER() OVER (PARTITION BY id_user) DESC;

demo on dbfiddle.uk

Upvotes: 3

Related Questions