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 django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
