SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select top 1 from each category
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kirank
Yak Posting Veteran

USA
52 Posts

Posted - 09/22/2012 :  06:42:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/22/2012 :  08:53:56  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/22/2012 :  12:48:00  Show Profile  Reply with Quote
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/

Go to Top of Page

kirank
Yak Posting Veteran

USA
52 Posts

Posted - 09/22/2012 :  14:11:32  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

276 Posts

Posted - 09/22/2012 :  15:15:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/22/2012 :  16:23:41  Show Profile  Reply with Quote

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


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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/05/2012 :  09:34:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Other methods http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000