CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1458109
user1458109

Reputation:

Why do I get the wrong output from this query?

Im trying to page results in ms sql server and I'm having some troubles getting the right result. I'd like to achieve the same result as mysql:s LIMIT and Iäm trying to use this model to do it: 

SELECT * FROM (
    SELECT TOP x * FROM (
        SELECT TOP y fields
        FROM table
        WHERE conditions
        ORDER BY table.field  ASC) as foo
    ORDER by field DESC) as bar
ORDER by field ASC

from: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/

The original query listing top 30 rows is the following: 

SELECT TOP 30 pt.[BSNR], t.ID, pt.RESDATUMTID, pt.LAND1, pt.HPL1, pt.ANKDATUMTID, pt.LAND2, pt.HPL2
  FROM [statistik2].[dbo].[ttrip] AS t
  JOIN [statistik2].[dbo].[tparttrip] AS pt 
  ON t.ID = pt.TRIP_ID
  WHERE t.DBKRDAT > '2012-06-27'
  ORDER BY pt.BSNR DESC, t.ID, pt.RESDATUMTID

And my attempt is: 

SELECT * FROM (
    SELECT TOP 10 * FROM (
        SELECT TOP 30 pt.ID AS PTID, pt.[BSNR], t.ID, pt.RESDATUMTID, pt.LAND1, pt.HPL1, pt.ANKDATUMTID, pt.LAND2, pt.HPL2
        FROM [statistik2].[dbo].[ttrip] AS t
        JOIN [statistik2].[dbo].[tparttrip] AS pt
        ON t.ID = pt.TRIP_ID
        WHERE t.DBKRDAT > '2012-06-27'
        ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID
    ) as pttt
    ORDER BY pttt.PTID DESC) AS ptttt
ORDER BY ptttt.PTID

Output from the queries: http://speedy.sh/5NQeq/sqloutput.txt

Can someone explain what I'm doing wrong?

Upvotes: 1

Views: 141

Answers (2)

Charleh
Charleh

Reputation: 14012

You can use ROW_NUMBER() to get a running count - this makes paging a lot easier...e.g.

(Note: this is for SQL 2005 and above - SQL 2000 doesn't support the row_number() function)

Assuming a table that has one column 'Name':

SELECT * FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY Name) as RunningVal, * FROM Names
) as Running 
WHERE RunningVal BETWEEN 5 AND 10

The whole triple-sort thing is a bit old school to be honest :P

In your case it would be something like:

SELECT * FROM (
    SELECT TOP 30 
        ROW_NUMBER() OVER (ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID) as RunningVal, 
        pt.ID AS PTID, 
        pt.[BSNR], 
        t.ID, 
        pt.RESDATUMTID, 
        pt.LAND1, 
        pt.HPL1, 
        pt.ANKDATUMTID, 
        pt.LAND2, 
        pt.HPL2 
    FROM [statistik2].[dbo].[ttrip] AS t 
    JOIN [statistik2].[dbo].[tparttrip] AS pt 
    ON t.ID = pt.TRIP_ID 
    WHERE t.DBKRDAT > '2012-06-27' 
    ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID
    ) as Running 
WHERE RunningVal BETWEEN 1 AND 10

In fact you can even get rid of the inner TOP and just use:

SELECT * FROM (
    SELECT     
        ROW_NUMBER() OVER (ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID) as RunningVal, 
        pt.ID AS PTID, 
        pt.[BSNR], 
        t.ID, 
        pt.RESDATUMTID, 
        pt.LAND1, 
        pt.HPL1, 
        pt.ANKDATUMTID, 
        pt.LAND2, 
        pt.HPL2 
    FROM [statistik2].[dbo].[ttrip] AS t 
    JOIN [statistik2].[dbo].[tparttrip] AS pt 
    ON t.ID = pt.TRIP_ID 
    WHERE t.DBKRDAT > '2012-06-27' 
    ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID
    ) as Running 
WHERE RunningVal BETWEEN @x AND @y

Where @x is the lower limit, and @y is the upper (BETWEEN is inclusive and ROW_NUMBER() starts at 1 so x = 1 and y = 10 will give you records 1-10)

Edit: Not sure what happens in MySql when you specify an OFFSET in LIMIT that is out of range (and the official docs don't seem to mention this one as far as I can see), but just be cautious that if you specify @x = 100 and @y = 110 and there are only 50 records in the table, you will get no results

Edit2: Added a SQL fiddle link if you want to fiddle it!

http://sqlfiddle.com/#!3/57808/3

Upvotes: 2

Andriy M
Andriy M

Reputation: 77657

Pay attention to the sample query.

The innermost subquery orders the results by field ASC. That is the main order through all the pages. The innermost subquery takes the top y rows in that order.

The middle-tier subquery reverses the order and takes the top x rows from the previous result, thus effectively taking the bottom x rows of the top y rows of the entire dataset.

The main query merely re-establishes the main order of rows.

You should do just the same. Your innermost query orders rows like this:

ORDER BY pt.BSNR DESC, t.ID DESC, pt.RESDATUMTID

If that is your main row order, then you should use the reversed version of it in your middle-tier subquery, i.e. like this:

ORDER BY pt.BSNR ASC, t.ID ASC, pt.RESDATUMTID DESC

It may not be that important how you order rows in your main query, but I would follow the sample and reversed the order again, making it match that of the innermost query.

Upvotes: 1

Related Questions