query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-06-11 : 07:59:57

orderid sequence_id weeks

101 1000 12
101 1001 14
102 1002 0
102 1003 0

I want the orderids such that if there are duplicate orderids then i should get the orderid with min(weeks)
and for the duplicate orderids if the weeks = 0, then i should get the orderid with min(sequence_id)

the output for the above table should be

101 1000 12
102 1002 0

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-11 : 08:53:19

You can use row_number function like this:

;with cte as
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY orderid ORDER BY weeks,sequence_id) AS RN
	FROM
		YourTable
)
SELECT
	orderid,
	sequence_id,
	weeks
FROM
	cte
WHERE
	RN=1;

Depending on the data - if you will have ties in sequence_id and you want to get all the ties, you may need to use RANK instead of ROW_NUMBER function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:22:09

[code]
SELECT t.*
FROM table t
CROSS APPLY (SELECT MIN(Weeks) AS MinWeeks,MIN(sequence_id) AS MinSeq
FROM table
WHERE orderid = t.orderid
)t1
WHERE t1.MinWeeks = t.Weeks
AND t1.MinSeq = t.sequence_id
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources