On 2013-03-26 15:54, Michael Manfre wrote:
> On Tue, Mar 26, 2013 at 12:40 PM, Anssi Kääriäinen
> deal with limit/offset. A generic approach would be nice to have,
> but I can't imagine a generic way that would let me generate the
> "SELECT ... FROM (SELECT ROW_NUMBER() OVER (...)) WHERE ..."
> monstrosity with lots of column aliasing that I currently
> construct.
The closest I've come is an ugly nested query using TOP (the TSQL
analog to LIMIT, but as mentioned earlier, there's no OFFSET
counterpart), and inverting the sort conditions:
-- want sorted by "a asc, b desc, c asc"
-- assuming LIMIT=10, OFFSET=20
select *
from (
select top 10 -- LIMIT
*
from (
select top 30 -- LIMIT+OFFSET=10+20
*
from tbl
order by a asc, b desc, c asc
) top_half
order by a desc, b asc, c desc -- note inversion
) reversed_top_half
order by a asc, b desc, c asc
It's been a while since I've done it, so it Works™, but (1) there's
the inevitable fence-posting error I'd have to verify, (2) it involves
sorting, reverse-sorting, then re-sorting (not exactly the speediest
operation), and (3) it's hideous. It doesn't seem to require the
column-aliasing you mention, and it is a fairly generic approach, but
I can't say I recommend it :-)
-tkc
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
