Retrieve TOP 3 Records (Special Case)

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

Transact-SQL (2000)

Retrieve TOP 3 Records (Special Case)

Author

Topic

AAnil
Starting Member

2 Posts

Posted - 2004-09-16 : 08:14:35

I have a table something like as below:

Rep_C Part_C HS_C Imp
004 251 020130 12.0
004 699 020230 54.0
004 826 020230 45.0
004 276 020230 43.0
004 528 020230 19.0
004 124 020329 72.0
004 276 020329 50.0
004 276 020442 15.0
004 364 020712 1115.0
004 528 021019 11.0
004 360 030269 13.0
004 528 030379 13.0

There are four fields and now I want only 3 records with "Imp" DESC for each "Rep_C" and "HS_C", i.e

Rep_C Part_C HS_C Imp
004 251 020130 12.0
004 699 020230 54.0
004 826 020230 45.0
004 276 020230 43.0
(004 528 020230 19.0) This record is not fetched
004 124 020329 72.0
004 276 020329 50.0
004 276 020442 15.0
004 364 020712 1115.0
004 528 021019 11.0
004 360 030269 13.0
004 528 030379 13.0

That means for each "Rep_C" and "HS_C" there will be 3 or less records depending on the desc values in "Imp". Actually there are many records in this case and I need to filter only top 3 for each "Rep_C" and "HS_C" depending on the desc values in "Imp".

Can any one help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 08:51:32

this should help:

Select *
From @myTable as t1
Where (Select count(imp) from @myTable Where Rep_C = t1.Rep_C and HS_C=t1.HS_C and imp >= t1.imp)<=3
Order By hs_c, imp desc

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

AAnil
Starting Member

2 Posts

Posted - 2004-09-16 : 09:11:18

Hi spirit1

I tried what you suggested but when the next Imp value in the table is more than the previous Imp value then it works fine, but when it is lower than previous value then it does not work.

I am planning to use this query in a view so that I can find the top3 for each rep_c and hs_c.

Thanks
Regards
Anil Adhawade

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-16 : 09:52:57

This is a bit lengthy method but if could help...

Select Rep_C, HS_C, Max(Imp) Imp
Into #Temp
From MyTable A
Group By Rep_C, HS_C

Insert #Temp (Rep_C, HS_C, Imp)
Select A.Rep_C, A.HS_C, Max(A.Imp) Imp
From MyTable a, #Temp B
Where a.Rep_C = B.Rep_C
and A.Hs_C = B.HS_C
and A.Imp < B.Imp
Group By A.Rep_C, A.HS_C

Insert #Temp (Rep_C, HS_C, Imp)
Select A.Rep_C, A.HS_C, Max(A.Imp) Imp
From MyTable a, (Select Rep_C, HS_C, Min(Imp) Imp
From #Temp
Group By Rep_C, HS_C ) B
Where a.Rep_C = B.Rep_C
and A.Hs_C = B.Hs_C
and A.Imp < B.Imp
Group By A.Rep_C, A.HS_C

Select A.* From MyTable A, #Temp B
Where A.Rep_C = B.Rep_C
and A.Hs_C = B.Hs_C
and A.Imp = B.Imp

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 11:50:42

I tested spirits and it works fine...


Declare @myTable table (Rep_C varchar(10), Part_C varchar(10), HS_C varchar(10), Imp float)
Insert Into @myTable Values ('004', '251', '020130', 12.0)
Insert Into @myTable Values ('004', '699', '020230', 54.0)
Insert Into @myTable Values ('004', '826', '020230', 45.0)
Insert Into @myTable Values ('004', '276', '020230', 43.0)
Insert Into @myTable Values ('004', '528', '020230', 19.0)
Insert Into @myTable Values ('004', '124', '020329', 72.0)
Insert Into @myTable Values ('004', '276', '020329', 50.0)
Insert Into @myTable Values ('004', '276', '020442', 15.0)
Insert Into @myTable Values ('004', '364', '020712', 1115.0)
Insert Into @myTable Values ('004', '528', '021019', 11.0)
Insert Into @myTable Values ('004', '360', '030269', 13.0)
Insert Into @myTable Values ('004', '528', '030379', 13.0)

Select 
	*,
	(Select count(imp) from @myTable Where Rep_C = A.Rep_C and HS_C=A.HS_C and imp >= A.imp)
From @myTable as A
Where (Select count(imp) from @myTable Where Rep_C = A.Rep_C and HS_C=A.HS_C and imp >= A.imp)<=3
Order By Rep_C, HS_C, Imp desc

Show an example where it doesn't work...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources