SQL: Top N rows for each X - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL: Top N rows for each X

Author

Topic

fun2oos
Starting Member

7 Posts

Posted - 2005-07-28 : 01:34:56

I want to retrive data from a table say TABLE1(ID,NAME).

Table has following data:

ID NAME
1 A
1 B
1 C
1 H
2 R
2 T
2 U
2 P
2 J
3 L
3 J
4 G
5 W
5 Q
5 S

I want to select TOP N (say N=3) rows for each ID (1,2,3,4,5),
i.e. output would be like

ID NAME
1 A
1 B
1 C
2 R
2 T
2 U
3 L
3 J
4 G
5 W
5 Q
5 S

SQL query should not be specific to oracle or SQL Server or other DB, but a general one...

Thnx.

fun2oos

fun2oos
Starting Member

7 Posts

Posted - 2005-07-28 : 01:37:24

It would be helpfull if various options/methods for doing this...

fun2oos
Starting Member

7 Posts

Posted - 2005-07-28 : 02:38:10

Here is the query, I tried. Its working, but 'TOP 3' is drawback in it

SELECT * FROM TABLE1 t1
WHERE [NAME] IN
(
SELECT TOP 3 [NAME] FROM TABLE1 t2
WHERE t1.[ID] = t2.[ID]
order by [ID] DESC
)
order by t1.[ID]

more general solution is welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-28 : 02:48:14

Something like this(suggested by spirit1)



declare @testt table (Dis_id int, Item_no int)
insert into @testt values(1,1)
insert into @testt values(1,2)
insert into @testt values(1,3)
insert into @testt values(1,4)
insert into @testt values(2,5)
insert into @testt values(2,6)
insert into @testt values(2,7)
insert into @testt values(2,8)
insert into @testt values(3,9)
insert into @testt values(3,10)
insert into @testt values(3,11)
insert into @testt values(3,12)

Select 	Dis_id, Item_no
From 	@testt as t1
Where 	(Select count(distinct Item_no) from @testt Where Dis_id = t1.Dis_id and Item_no >= t1.Item_no)<=3 
Order By Dis_id, Item_no

Madhivanan

Failing to plan is Planning to fail

fun2oos
Starting Member

7 Posts

Posted - 2005-07-28 : 06:40:40

Thnx a lot...

Now, I want to be more specific in selecting the records....

Select * From TABLE1 as t1
Where (
Select count(distinct [NAME]) from TABLE1 Where [ID] = t1.[ID] and [NAME] >= t1.[NAME]
) <= 2
Order By t1.[ID] ASC, t1.[NAME] ASC

This is returing last N (=3) records for each [ID]...
what if first N records needed...

:-W

fun2oos

Tell if u know, it will help u revise the topic,
and ask if u don't know, as it will let u know...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 06:44:22

declare @n int
set @n = 5 -- nubmer of records you want returned for each group

Select * From TABLE1 as t1
Where (
Select count(distinct [NAME]) from TABLE1 Where [ID] = t1.[ID] and [NAME] >= t1.[NAME]
) <= @n
Order By t1.[ID] ASC, t1.[NAME] ASC

Go with the flow & have fun! Else fight the flow

fun2oos
Starting Member

7 Posts

Posted - 2005-07-28 : 07:22:37

actually I didn't mean that...

I want output as
(reffered table TABLE1 shown at begning of thread)

1 A
1 B
...

not

1 C
1 H

as its comming in the above query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-28 : 07:25:54

Use [NAME] <= t1.[NAME]
instead of
[NAME] >= t1.[NAME]

Madhivanan

Failing to plan is Planning to fail

fun2oos
Starting Member

7 Posts

Posted - 2005-07-29 : 04:55:30

Thnx.

What if I want 3 random records of each ID, (say, 3 out of M records of each ID)

fun2oos
:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 05:17:43

Use <= 3
instead of <= 2

Madhivanan

Failing to plan is Planning to fail

Harshitha29
Starting Member

2 Posts

Posted - 2005-07-29 : 13:25:43

Hi,

Did you get Random numbers in each Id?
I have same problem. I havbe cno & company.I have 4 companies. I need 8 cno 2 from each company.

Ex:
cno company
1 us
2 in
3 us
4 tx
5 in
6 tx
5 ab
7 us
8 in
.
.so on

I need some random cno in each company.
3 us
7 us
..
Select cno, company From table as t1
Where (
Select count(distinct [cno]) from table Where [company] = t1.[company] and [cno] <= t1.[cno]
) <3 and date between '2005-05-01 00:00:00.000' and '2005-05-31 00:00:00.000'
Order By t1.[company] ASC, t1.[company] ASC

Could you please help me to write this query? I tried ur query but I am getting only from one company. I need 2 cno from each company.
This I need it very urgently.

Thank You,
Harshitha

Harshitha29
Starting Member

2 Posts

Posted - 2005-07-29 : 17:56:13

Hi,

I got solution to this problem.
Thanks to everyone.

Thank you,
Harshitha

fun2oos
Starting Member

7 Posts

Posted - 2005-07-31 : 23:50:38

Harshitha29,
then do tell us ur solution....

:-w

fun2oos

GSWilliams1906
Starting Member

4 Posts

Posted - 2006-08-30 : 15:47:10

I am not sure if I should start a new thread or latch on to this one, but here it goes. I have a similar problem:

I have the following tables. First is a pricing table:

Terminal Supplier Product Day Price
Terminal 1 Supplier 1 Product 1 Day 1 2.30
Terminal 1 Supplier 2 Product 1 Day 1 2.35
Terminal 1 Supplier 3 Product 1 Day 1 3.00
Terminal 1 Supplier 4 Product 1 Day 1 2.78
Terminal 1 Supplier 5 Product 1 Day 1 2.64
Terminal 2 Supplier 1 Product 1 Day 1 2.30
Terminal 2 Supplier 2 Product 1 Day 1 2.35
Terminal 2 Supplier 3 Product 1 Day 1 2.58
Terminal 2 Supplier 4 Product 1 Day 1 2.58
Terminal 2 Supplier 5 Product 1 Day 1 2.65
Terminal 3 Supplier 1 Product 1 Day 1 2.30
Terminal 3 Supplier 2 Product 1 Day 1 2.35
Terminal 3 Supplier 3 Product 1 Day 1 3.00
Terminal 3 Supplier 4 Product 1 Day 1 2.78
Terminal 3 Supplier 6 Product 1 Day 1 2.59
Terminal 4 Supplier 1 Product 1 Day 1 2.30
Terminal 4 Supplier 2 Product 1 Day 1 2.29
Terminal 4 Supplier 3 Product 1 Day 1 3.01
Terminal 4 Supplier 5 Product 1 Day 1 2.64
Terminal 4 Supplier 7 Product 1 Day 1 2.45
Terminal 5 Supplier 1 Product 1 Day 1 2.30
Terminal 6 Supplier 2 Product 1 Day 1 2.35
Terminal 6 Supplier 3 Product 1 Day 1 3.00
Terminal 6 Supplier 6 Product 1 Day 1 2.61
Terminal 6 Supplier 7 Product 1 Day 1 2.54
Terminal 1 Supplier 1 Product 1 Day 2 2.31
Terminal 1 Supplier 2 Product 1 Day 2 2.36
Terminal 1 Supplier 3 Product 1 Day 2 3.00
Terminal 1 Supplier 4 Product 1 Day 2 2.78
Terminal 1 Supplier 5 Product 1 Day 2 2.65
Terminal 2 Supplier 1 Product 1 Day 2 2.31
Terminal 2 Supplier 2 Product 1 Day 2 2.37
Terminal 2 Supplier 3 Product 1 Day 2 2.58
Terminal 2 Supplier 4 Product 1 Day 2 2.78
Terminal 2 Supplier 5 Product 1 Day 2 2.64
Terminal 3 Supplier 1 Product 1 Day 2 2.31
Terminal 3 Supplier 2 Product 1 Day 2 2.36
Terminal 3 Supplier 3 Product 1 Day 2 3.00
Terminal 3 Supplier 4 Product 1 Day 2 2.78
Terminal 3 Supplier 6 Product 1 Day 2 2.60
Terminal 4 Supplier 1 Product 1 Day 2 2.31
Terminal 4 Supplier 2 Product 1 Day 2 2.36
Terminal 4 Supplier 3 Product 1 Day 2 3.01
Terminal 4 Supplier 5 Product 1 Day 2 2.64
Terminal 4 Supplier 7 Product 1 Day 2 2.48
Terminal 5 Supplier 1 Product 1 Day 2 2.31
Terminal 6 Supplier 2 Product 1 Day 2 2.36
Terminal 6 Supplier 3 Product 1 Day 2 3.00
Terminal 6 Supplier 6 Product 1 Day 2 2.61
Terminal 6 Supplier 7 Product 1 Day 2 2.73

I have a customer who want to receive the lowest price for the given product for each terminal.

Terminal Supplier Product Day Price
Terminal 1 Supplier 1 Product 1 Day 1 2.30
Terminal 1 Supplier 2 Product 1 Day 1 2.35
Terminal 1 Supplier 5 Product 1 Day 1 2.64
Terminal 2 Supplier 1 Product 1 Day 1 2.30
Terminal 2 Supplier 2 Product 1 Day 1 2.35
Terminal 2 Supplier 3 Product 1 Day 1 2.58
Terminal 2 Supplier 4 Product 1 Day 1 2.58
Terminal 3 Supplier 1 Product 1 Day 1 2.30
Terminal 3 Supplier 2 Product 1 Day 1 2.35
Terminal 3 Supplier 6 Product 1 Day 1 2.59
Terminal 4 Supplier 2 Product 1 Day 1 2.29
Terminal 4 Supplier 1 Product 1 Day 1 2.30
Terminal 4 Supplier 7 Product 1 Day 1 2.45
Terminal 5 Supplier 1 Product 1 Day 1 2.30
Terminal 6 Supplier 2 Product 1 Day 1 2.35
Terminal 6 Supplier 7 Product 1 Day 1 2.54
Terminal 6 Supplier 6 Product 1 Day 1 2.61

Now in the pricing table there are other products. When I do a Top N where in equal 3. I only get back three prices for all terminals.
When I do a select * from customer template WHERE (Top 3 Select * from pricing table where terminal = customer template terminal ) I get back the three prices, but multiple products. I only want 1 product. When I add in the and where product = product 1 I get an empty dataset.

I know the query I have above are not the right syntax, it just to give you an idea of what I’m trying to do. I want the lowest N price for product 1 from each terminal based upon the customers template who one customer may want all terminals or another customer may want terminal 1,2, and 5 only. Can anyone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-30 : 18:29:24

please start a new thread. It will catch more attention

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources