Top 5 of column distinct Value

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

General SQL Server Forums

New to SQL Server Programming

Top 5 of column distinct Value

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2011-01-19 : 01:50:25

Dear Friends,

I want top 5 of each modelId, the table is as follows:

Id------ ModelId
3001------ abc
3002------ abc
3003------ xyz
3004------ xyz
3005------ xyz
3006------ xyz
3007------ xyz
3008------ xyz
3009------ xyz
3010------ xyz
3011------ xyz
3012------ ppp
3013------ ppp
3014------ ppp
3015------ ppp
3016------ ppp
3017------ ppp
3018------ ppp
3019------ ppp
3020------ ppp
3021------ abc
3022------ abc
3023------ abc
3024------ abc
3025------ abc
3026------ abc
3027------ abc

Expected Output:

Id------ ModelId
3001------ abc
3002------ abc
3021------ abc
3022------ abc
3023------ abc
3003------ xyz
3004------ xyz
3005------ xyz
3006------ xyz
3007------ xyz
3012------ ppp
3013------ ppp
3014------ ppp
3015------ ppp
3016------ ppp

In a real world senario i need to fetch "top 20" and actaul table contains more than 50,000 records, so needs to take care of performance also. Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-19 : 02:17:23

hopefully you are using SQL 2005 /2008


select *
from
(
    select *, row_no = row_number() over (partition by ModelId order by Id)
    from   yourtable
) d
where d.row_n <= 5

KH
[spoiler]Time is always against us[/spoiler]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-01-19 : 06:23:42

Use Common Table Expression for such problems

WITH CTE_TABLE
As (SELECT ID,ModelId,ROW_NUMBER() OVER (ORDER BY ID) AS RowNUm FROM YourTableNameHere)
SELECT * FROM CTE_TABLE WHERE RowNUm <=5

--------------------------
http://connectsql.blogspot.com/

amodi
Yak Posting Veteran

83 Posts

Posted - 2011-01-19 : 07:44:37

Excuse me for delayed feeback.

Thanks a million khtan!

Thanks lionofdezert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources