CODEWITHSUNDEEP
sqlsql-server-2005pagination
abc
abc

Reputation: 2437

sql query limit by amount of values in a column

I work on a SQL Server 2005, where I have two tables, like this:

Table1

ID (Guid) Primarykey
something1 (string)
something2 (string)

And

Table2

ID (Guid) Primarykey
Table1ID (Guid) is a ForeignKey to Table1
OrderNr (int)
something1 (string)
something2 (string)

Now I have to do a select query (which gives me hundreds of results), basically something like: SELECT * from Table2 where something1 = 'foo' order by Table1ID, OrderNr

Specially the order by is important, I can't change that. And the statement is in real much more complicated with a lot of joins, subselects and wheres and so on, but they are not needed here...

My problem is, that I need a special paging: Just select all the rows with the 20 different Table1IDs starting from ID 10 to ID 15. (That means I want a paging, but not on rowcount but on this columns unique values)

If I had a nested select or a function call which adds the iterated number of the selectstatement i could use a between, but how can I count the unique ids and add it to the query up there?

Thank you for your help, Karl

Upvotes: 0

Views: 119

Answers (2)

abc
abc

Reputation: 2437

Actually it is similar to what Tim Schmelter said, but not a ROW_NUMBER() OVER (PARTITION BY... but a DENSE_RANK() OVER (ORDER BY Table1ID) AS rank

Explanation: DENSE_RANK does exactly what I want. It ranks the table, in my case by Table1ID. The effect is, each Table1ID got its own ranknumber. With rank between 10 and 15 I get exactly what I wanted.

Tank you, Karl

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use ROW_NUMBER with Partition By:

WITH CTE AS
(
   SELCT Columns, ...
       , RN = ROW_NUMBER() OVER (PARTITION BY Table1IDs ORDER BY ID)
   FROM dbo.Table
)
SELECT Columns, ...
FROM CTE
WHERE RN BETWEEN 10 AND 15

(or i have misunderstood your requirement which is not very unlikely)

Upvotes: 2

Related Questions