Search code examples
sqlsql-serverdistinctbetweenrownum

retrieve second 10 UNIQUE rows in SQL

I want to retrieve the second 10 UNIQUE rows in my sql database, for selecting the second 10 rows, I use the next script:

SELECT col1, col2 FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 10 AND 19

But now I want the UNIQUE rows in that set, when I use DISTINCT like in the next example, the DISTINCT also goes over RowNum which makes every row a DISTINCT row... how can I exclude the RowNum from my DISTINCT?

SELECT col1, col2 FROM (
    SELECT DISTINCT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 10 AND 19
Solution

  • You need to use group by instead of distinct, as they are applied in different moments of query execution. Of course, since col1 and col2 are in N:1 relation with ID you need to tell SQL which exact ID to use when numbering rows. This can probably be MAX(ID). So:

    SELECT col1, col2 FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY MAX(ID)) AS RowNum
    FROM MyTable
    GROUP BY col1, col2
) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 10 AND 19