Thread: Last x records
Moin, is there any way to get the last x records of an query result? I can use "... order by x DESC LIMIT n" but I need ASC not DESC, the last records in the correct order. Many thanks Matthias -- Matthias Teege -- matthias@mteege.de -- http://emugs.de make world not war PGP-Key auf Anfrage
From: "Matthias Teege" <matthias@mteege.de> > Moin, > > is there any way to get the last x records of an query > result? > > I can use "... order by x DESC LIMIT n" but I need ASC not > DESC, the last records in the correct order. If you know how many records there are you could use "LIMIT n OFFSET m" where m = number of records - n Otherwise, the only thing that I can think of is to set up a view that does the LIMIT n on a DESC then sort ASC when selecting on that view - might work. - Richard Huxton
"Richard Huxton" <dev@archonet.com> writes: > From: "Matthias Teege" <matthias@mteege.de> >> is there any way to get the last x records of an query >> result? > Otherwise, the only thing that I can think of is to set up a view that does > the LIMIT n on a DESC then sort ASC when selecting on that view - might > work. Not in 7.0.* or before --- there is just plain no way to get multiple levels of sorting in a query. In 7.1 you can do it like this: SELECT * FROM (SELECT ... ORDER BY foo DESC LIMIT n) ss ORDER BY foo; A workaround in 7.0.* is to use a temp table: SELECT ... INTO TEMP t1 ... ORDER BY foo DESC LIMIT n; SELECT * FROM t1 ORDER BY foo; DROP TABLE t1; The extra sort step shouldn't affect the speed much as long as n is small. regards, tom lane