Обсуждение: 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