CODEWITHSUNDEEP
mysqlsql-order-by
ItsKasper
ItsKasper

Reputation: 35

Select every 'nth row in descending order

SELECT * FROM ( SELECT @row := @row +1 AS rownum, [column name] FROM ( SELECT * FROM [table name] ) WHERE rownum % 5 = 1

This does indeed return every 5th row, but in ascending order. What I want is that it first gets all the data, put them in descending order and THEN apply the filter.

If you filter it first and then put it in descending order, it will not start with the latest data added (4/5th of the time).

I would like to know how one should do this.

Thanks in advance

Edit: For people with the same problem, this is what I used: 

SELECT * FROM
(SELECT rank, id, Temperature FROM 
(SELECT *, @rownum := @rownum + 1 AS rank FROM temperature_room1, 
(SELECT @rownum := 0) r) AS T ORDER BY id DESC) AS J WHERE rank % 5 = 1
  1. Select everything from:
  2. Select rank, id and Temperature from:
  3. Select everything and rownumber as rank from the table as t ordered by ID in descending order
  4. Finally, only output the row numbers which can be divided by 5 and the remainder is 1

Don't quote me on this, I'm a big noob regarding SQL stuff. It works for me, so I'm happy.

Upvotes: 1

Views: 693

Answers (1)

xQbert
xQbert

Reputation: 35323

seems like you just need an order by dec on the desired column in one of the three queries. I think the second one as order by applies to the select at the same level. ans since you want your rownum ordered desc... seems like that's the place...

SELECT * 
FROM ( SELECT @row := @row +1 AS rownum, [column name] 
       FROM ( SELECT * FROM [table name] )
       ORDER BY [column name] desc
     )

WHERE rownum % 5 = 1

Upvotes: 1

Related Questions