CODEWITHSUNDEEP
phpmysqlsqlgroup-by
MeAndMySelf
MeAndMySelf

Reputation: 173

How to Group by user_id and order by desc

Hello i am aware this is very basic but right now i am so confused i want to GROUP BY user_id ORDER BY id DESC LIMIT 4 (i have posted short example of table and my query)

My table looks like where id is auto increment i only save user_id i only want to take one user_id only once which is lastest entry in database ignore other.

Table
---------------------
id     |   user_id  |
---------------------
13     |    25      |
12     |    36      |
11     |    25      |
10     |    42      |
9      |    95      |
8      |    25      |
7      |    95      |
---------------------
so on

I have tried this 

SELECT * FROM  `table` GROUP BY user_id ORDER BY `id` DESC LIMIT 4

I want it to output 25,36,42,95 i have also tried many experiments but nothing seems to be working. Do i need timestamp or something to make it in group? or what query will work?

Upvotes: 1

Views: 4058

Answers (5)

Padmanathan J
Padmanathan J

Reputation: 4620

Try this 

SELECT MAX(id) as countid , `user_id` from `table`
GROUP BY `user_id`
ORDER BY countid  DESC
LIMIT 20

Upvotes: 0

Spell
Spell

Reputation: 8658

  1. In your example you need to get result like 25,36,42,95, but sort by ID field. If you will sort by it you will get different result.

  2. You get a grouping in your query so you cannot use columns that not chosen in query (id is ommited there)

  3. If you still need to order by id, i think that you need to add order by MAX('id') or MIN('id')

In your case i`ll make next query:

SELECT user_id FROM  `table` GROUP BY user_id ORDER BY MAX(`id`) DESC LIMIT 4

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You are doing a partial GROUP BY which does not work the way you expect. Here is a query which produces the desired results:

SELECT MAX(id) AS MAXID, user_id
FROM `table`
GROUP BY user_id
ORDER BY MAXID DESC
LIMIT 4

The behavior is explained here:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. [...] You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

Upvotes: 8

Manish Sapkal
Manish Sapkal

Reputation: 6191

SELECT * FROM ( SELECT 13 id, 25 UserID UNION ALL SELECT 12 id, 36 UserID UNION ALL SELECT 11 id, 25 UserID UNION ALL SELECT 10 id, 42 UserID UNION ALL SELECT 09 id, 95 UserID UNION ALL SELECT 08 id, 25 UserID UNION ALL SELECT 07 id, 95 UserID ) a GROUP BY userid ORDER BY id DESC LIMIT 4

Upvotes: -2

Rajiv Ranjan
Rajiv Ranjan

Reputation: 1869

In stead of grouping, you can use distinct, like below sql query.

SELECT distinct(user_id) FROM  `table` ORDER BY `id` DESC LIMIT 4

Upvotes: -1

Related Questions