CODEWITHSUNDEEP
mysql
Harsha Jasti
Harsha Jasti

Reputation: 1254

Order by one column and then by another

This is my table:

ID UserID Client Time(timestamp)

1  25     Acer   2017-09-13 09:09:13
2  21     Lenovo 2017-09-13 12:09:32
3  20     HP     2017-09-13 14:04:26
4  21     Dell   2017-09-13 17:04:23
5  25     Apple  2017-09-13 17:09:46
.
.
.

I want the result to be ordered by timestamp first, fetching the first 5 records, and then ordering by user id like this

ID UserID Client Time(timestamp)
5  25     Apple  2017-09-13 17:09:46
1  25     Acer   2017-09-13 09:09:13
4  21     Dell   2017-09-13 17:04:23
2  21     Lenovo 2017-09-13 12:09:32
3  20     HP     2017-09-13 14:04:26

i tried this query

select * from table order by time Desc, UserID LIMIT 5;

but it doesn't seem to work, instead i get this as the result

ID UserID Client Time(timestamp)
5  25     Apple  2017-09-13 17:09:46
4  21     Dell   2017-09-13 17:04:23
3  20     HP     2017-09-13 14:04:26
2  21     Lenovo 2017-09-13 12:09:32
1  25     Acer   2017-09-13 09:09:13

I am not sure where i am going wrong.

Upvotes: 4

Views: 7356

Answers (4)

jeffzhao
jeffzhao

Reputation: 3

OK. You need to fetch the first 5 records ordered by timestamp, and reorder them by userID. Here is the code:

SELECT * FROM (
    SELECT* FROM 
    table1 
    ORDER BY time DESC 
    LIMIT 5) firstfive 
ORDER BY UserID DESC

Upvotes: 0

Ravi
Ravi

Reputation: 31417

You need in-line view, where first get the 5 rows order by time and then UserID

SELECT * FROM
(
    SELECT *
    FROM table
    ORDER BY time DESC
    LIMIT 5
)
ORDER BY UserID DESC

So, above query has nested select which will execute first and get you 5 rows ordered descending by time. Then, we will use this resultset to outer query and order the row by UserID

Upvotes: 0

Iurii Drozdov
Iurii Drozdov

Reputation: 1755

You can try to do this:

SELECT * FROM
(
    SELECT *
    FROM table
    ORDER BY time DESC
    LIMIT 5
) AS firstusers
ORDER BY UserID

Upvotes: 4

user8767597
user8767597

Reputation:

It should be order by UserID DESC, time Desc instead:

select * 
from table1 order by  UserID DESC, time Desc;

demo

This will give you the same order you are looking for:

| ID | UserID | Client |                 Time |
|----|--------|--------|----------------------|
|  5 |     25 |  Apple | 2017-09-13T17:09:46Z |
|  1 |     25 |   Acer | 2017-09-13T09:09:13Z |
|  4 |     21 |   Dell | 2017-09-13T17:04:23Z |
|  2 |     21 | Lenovo | 2017-09-13T12:09:32Z |
|  3 |     20 |     HP | 2017-09-13T14:04:26Z |

Upvotes: 5

Related Questions