how to orderby after applying limit in sql

Question

I need to get the information from DB in such a way, the limit should be 3 rows and out of which i want to sort by descending order. I used

select * from table where coloumn = 'Myfilter' order by serialNumber desc limit 3

after the execution I am not getting the latest three records rather the first three records ordered by descending.

*after the execution I am not getting the latest three records ...* based on which field? SQL tables are unordered sets of data. You always need a field to define row order. Which is this field in your case? — Giorgos Betsos, Nov 25 '15 at 06:57
he probably wants to take the first 3 records firsrt and reorder it to which who is latest among the 3 — Kelvin Barsana, Nov 25 '15 at 06:59
@KelvinBarsana What does *first* mean? MySQL doesn't keep track of records in any predefined order. You *always* need a reference field to define row order. — Giorgos Betsos, Nov 25 '15 at 07:01
@GiorgosBetsos maybe an additional condition should be applied before limiting the result to 3 and reordering the result — Kelvin Barsana, Nov 25 '15 at 07:04
this will helpful for your reason http://stackoverflow.com/questions/4708708/mysql-order-by-limit — Kasun Gamage, Nov 25 '15 at 09:15

score 2 · Answer 1 · edited Nov 25 '15 at 08:28

2

Applying limit before order by

SELECT * FROM (SELECT * FROM table WHERE coloumn = 'Myfilter' ORDER BY serialNumber LIMIT 3) a ORDER BY serialNumber DESC

edited Nov 25 '15 at 08:28

Strawberry

33,750
13
40
57

answered Nov 25 '15 at 06:54

Kelvin Barsana

824
12
28

@Strawberry he didn't indicate that there should be an order by before limiting the results, so that's the only query i can create.. order by in the subquery can be added if he'll require – Kelvin Barsana Nov 25 '15 at 07:30
Regardless, we know that there should be one. Rows in an RDBMS represent unordered sets so wherever there is LIMIT there must also be ORDER BY. But if you don't want my vote, that's fine too ;-) – Strawberry Nov 25 '15 at 07:40
@Strawberry okay got your point and since that i want your vote, thanks – Kelvin Barsana Nov 25 '15 at 08:21
thnx for the reply, `SELECT * FROM (SELECT * FROM table WHERE coloumn='myFilter' ORDER BY serialnumber desc LIMIT 3) a ORDER BY serialnumber asc` solves my problem – Kanni1303 Nov 25 '15 at 08:59
Could you please tell, what does the `a` means? – Sabbir Sobhani May 29 '22 at 18:13
1

@Sobhani, it is an alias for the subquery if I'm not mistaken which I believe is required. This answer is quite old that is why I am not so sure. – Kelvin Barsana May 30 '22 at 04:09
Thanks, it's old, so is there any replacement for the `a` now? or is it still relevant? – Sabbir Sobhani May 30 '22 at 04:40
1

@Sobhani I think it is still relevant as long as it works.. syntax does not update much as for how mysql rolls – Kelvin Barsana May 30 '22 at 06:08
1

as for literal replacement for 'a', I think it could be any character – Kelvin Barsana May 30 '22 at 06:09

score 1 · Accepted Answer · answered Nov 25 '15 at 09:06

This query solves my question thank you all for suggestions,

SELECT * FROM (SELECT * FROM table WHERE coloumn='myFilter' ORDER BY serialnumber desc LIMIT 3) a ORDER BY serialnumber asc

the query uses to select the latest 3 rows ordered by big to small serial number then again the selected rows order where reversed, thnx @Kelvin Barsana

score -3 · Answer 3 · answered Nov 25 '15 at 06:49

-3

"SELECT * FROM table WHERE coloumn = 'Myfilter' ORDER BY serialNumber DESC LIMIT 3";

answered Nov 25 '15 at 06:49

Judd Choa

19
1
4

how to orderby after applying limit in sql

3 Answers3