November 26, 2008 at 2:12 am
Hello all,
Long time lurker, first time post.
I'm looking for a bit of help with a query the table looks like:
ID int
NAME varchar(20)
PARENTID int
ID is the primary key & clustered index.
What I want is the first 10 records of each different name, so if the data looked like
1 BOB 45
2 BOB 543
3 BOB 712
4 BOB 976
5 BOB 126
6 BOB 666
7 BOB 845
8 BOB 185
9 BOB 24
10 BOB 964
11 BOB 34
12 BOB 235
13 FRED 345
14 FRED 678
I'd like to see the following as the result from the query:
1 BOB 45
2 BOB 543
3 BOB 712
4 BOB 976
5 BOB 126
6 BOB 666
7 BOB 845
8 BOB 185
9 BOB 24
10 BOB 964
13 FRED 345
14 FRED 678
I've achieved it, but its horrendously slow:
select * from
(
select *, row_number() over (partition by NAME order by NAME desc) as counter from TABLEA
) qry
where qry.counter < 11
order by qry.ID
This takes around a minute ot return 40 rows from about 1.2 million rows in the table.
If I look at the execution plan its sorting a scan on the clustered index (ID) but its the sort that is taking the time, I'm sure there is a better way to do this but all the over .. partition by.. examples I find are using group, which I can use.
Any help appreciated!
November 26, 2008 at 6:23 am
All that lurking and you haven't seen us sending people to the same post over and over? :hehe: Please refer to the article in my signature for an example of how to post table DDL and sample data.
Also, are you saying you "can't" use group by?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply