select top 1 from each category

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

select top 1 from each category

Author

Topic

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-09-22 : 06:42:55

Hi , plz help me on below query

i have some category id , out of that i just want the top 1 records from each category

for ex:

select * from prod_details where catid in (9,3,5,2)

here i have catid as -9,3,5,2
i just want the single records from each category , can any one suggest me the query on it.

---------------------------

http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 08:53:56

You can use one or the other of the queries shown below. These will work only on SQL 2005 or higher:

-- 1
SELECT
	b.*
FROM
	YourTable a
	CROSS APPLY
	(
		SELECT TOP 1 *
		FROM YourTable b
		WHERE b.Catid = a.CatId
		ORDER BY col1,col2 -- columns you want to use to determine which one to pick
	) b

-- 2	
SELECT
	a.Col1,
	a.Col2,
	a.CatID
FROM
(
	SELECT b.*,
	ROW_NUMBER() OVER 
	(
		PARTITION BY b.catid
		ORDER BY col1, col2 -- columns you want to use to determine which one to pick
	) RN
	FROM YourTable b
) a
WHERE a.RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 12:48:00

first determine rule or retrieval of top 1. what are columns based on which you want to determine the top 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-09-22 : 14:11:32

quote:
Originally posted by visakh16

first determine rule or retrieval of top 1. what are columns based on which you want to determine the top 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thank you for reply,

@sunita, thanx i will try this CROSS APPLY is new for me. i will try on this and let u know ,

@visakh - i m retrieving records base on record added by time(column name addeddate)

---------------------------

http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-22 : 15:15:24

Another way could be:


select b.*
  from (select catid
              ,max(addeddate) as addeddate
          from tablename
         group by catid
       ) as a
  inner join tablename as b
          on b.catid=a.catid
         and b.addeddate=a.addeddate

sunitabeck's solutions is probably more efficiant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 16:23:41

[code]
SELECT CatID, other columns...
FROM
(
SELECT *,
MAX(addeddate) OVER (PARTITION BY catid) AS MaxDate
FROM YourTable b
) a
WHERE addeddate = MaxDate;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 09:34:30

Other methods http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources