sql query limit by amount of values in a column

Question

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

@AshBurlaczenko: Why should a LINQ answer answer a sql question? — Tim Schmelter, Oct 18 '12 at 11:16
@TimSchmelter, because the answer also includes how to do it in sql. — Ash Burlaczenko, Oct 18 '12 at 11:25

score 2 · Answer 1 · answered Oct 18 '12 at 11:11

2

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)

answered Oct 18 '12 at 11:11

Tim Schmelter

450,073
74
686
939

Tahnk you, with your answer you helped me a lot. It was not that, what I needed but I came to the right websites due to your answer ;) – abc Oct 18 '12 at 11:35

score 1 · Accepted Answer · answered Oct 18 '12 at 11:47

1

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

answered Oct 18 '12 at 11:47

abc

2,285
5
29
64

You should accept your own answer if it's the better one. `DENSE_RANK()` is a very useful, lesser-known ranking function. Here's an [interesting blog post I've written recently](http://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/), explaining how `DENSE_RANK()` relates to `SELECT DISTINCT` the way `ROW_NUMBER()` relates to `SELECT`. That's not exactly the problem you're solving but might add some interesting background info – Lukas Eder Oct 27 '13 at 10:33

sql query limit by amount of values in a column

2 Answers2