No Results returned for ROW_NUMBER() query

Question

I am getting "no results returned" for the following query:

SELECT 
    Referer 
FROM 
    (SELECT 
        ROW_NUMBER() OVER (ORDER BY CT.Referer ASC) AS RowNum, 
        CT.Referer, CT.LastModified
     FROM 
        ClickTrack CT 
     JOIN 
        OrderTrack OT ON OT.ClickTrackID = CT.ClickTrackID              
     GROUP BY 
        CT.Referer, CT.LastModified
     HAVING 
        LEN(CT.Referer) > 0) as num 
WHERE 
    RowNum = 1 
    AND LastModified BETWEEN '07/06/2013' and '08/05/2013'

Curiously, when I leave off RowNum = 1, I get the full list of values. I need to get one at a time though to assign to a variable and drop into a temporary table.

The end query will be in a while loop using scalar variables in place of the date ranges and RowNum comparison.

Any help is appreciated. Thank you!

can you provide some example data? I used data I thought might make sense given the schema, but I'm always getting the results I expect. — CD Jorgensen, Aug 07 '13 at 21:58
also, what do you see when you change your select clause to be "SELECT RowNum, Referer"? Does RowNum have a value? — CD Jorgensen, Aug 07 '13 at 22:08
***What database*** (and which version) is this for? MySQL? Postgres? DB2? Oracle? SQL Server? Something else? — marc_s, Aug 08 '13 at 04:59
I'm working with MS Server 2008. Some sample data would be: Columns: [clicktrackid referer lastmodified] Data: [1 www.google.com 7/10/2013 19:31] [2 www.yahoo.com 7/11/2007 10:20] [3 www.google.co.uk 7/13/2013 15:33] [4 www.aol.com 8/16/2013 12:54] — Mike, Aug 08 '13 at 13:12

score 1 · Accepted Answer · answered Aug 07 '13 at 21:59

I'm thinking RowNum 1 may not have a date between your selections. Maybe put the date selection inside so that you know that the first one matches.

    SELECT Referer 
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY CT.Referer ASC) 
    AS RowNum, CT.Referer, CT.LastModified
    FROM ClickTrack CT 
    JOIN OrderTrack OT ON OT.ClickTrackID = CT.ClickTrackID    
    WHERE CT.LastModified BETWEEN '07/06/2013' and '08/05/2013'          
    GROUP BY CT.Referer, CT.LastModified
    HAVING LEN(CT.Referer) > 0) as num 
    WHERE RowNum = 1

Thank you sir, I believe you are correct. I tip my hat to you! — Mike, Aug 08 '13 at 13:31

No Results returned for ROW_NUMBER() query

1 Answers1