SQL last rows

NZ_Kiwis

Hey,

I have a MySQL table and i have many rows. I have a column set_id now I want to do a query which will pull the last 5 rows in the table but each one with different set_id's

ie if the last 6 rows were

Row | Set_id
500 = 2
501 = 4
502 = 6
503 = 4
504 = 8
505 = 9

it would get the rows 500, 501, 502, 504 and 505.... as they have different set_id's

how can this be done????

mrhappiness

SELECT t1.row, t1.set_id
FROM qwertz t1
 LEFT JOIN qwertz t2
  ON t1.set_id = t2.set_id AND t1.row > t2.row
WHERE t2.set_id IS NULL

ccgorman

SELECT * FROM table GROUP BY Set_id ORDER BY Row DESC LIMIT 5;

I think this will do it for you? It will ored by row DESC, so most recent first. It will give a unique Set_id and will limit the result to 5 rows.

mrhappiness

depends on what exactly he wants to have

my solution returns the lowest "row" for each set_id, changing the > to < will return the highes "row" for each set_id

your solution returns one "row" for each set_id but not neccessarily the highest or lowest one

ccgorman

Very true, I didn't think of that, whould this work as well to get the lowest and highest? Not sure if I have the syntax correct?

SELECT DISTINCT(Row, Set_id) FROM table ORDER BY Row DESC LIMIT 5;

SELECT DISTINCT(Row, Set_id) FROM table ORDER BY Row ASC LIMIT 5;

mrhappiness

wrong syntax
if you corrected the syntax it wouldn't return the desired result either as 501,4 and 503, 4 are distinct...