Select Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Query Help

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-10-18 : 12:59:38

If I have a table of values:

Key Type Priority
7 1 6
8 2 4
11 3 4
15 4 8
16 3 3

I want to select all rows that have a unique Type. if the Type is the same as another row, i want to return the one with the lower Priority value. In the case above, Keys 11 and 16 have the same Type. In that case, I'd want to return 16 since it has a lower Priority value.

how would i do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-18 : 13:07:10

SELECT Type, MIN(Priority) AS Priority
FROM YourTable
GROUP BY Type

If you also want the key column, then you'll need a derived table:


SELECT t1.Key, t2.Type, t2.Priority
FROM YourTable t1
INNER JOIN
(
	SELECT Type, MIN(Priority) AS Priority
	FROM YourTable
	GROUP BY Type
) t2
ON t1.Type = t2.Type AND t1.Priority = t2.Priority

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-18 : 13:09:48

[code]
Declare @T Table ([Key] int, Type int, Priority int)
Insert into @T
SELECT 7, 1 ,6 union all
SELECT 8, 2 ,4 union all
SELECT 11, 3 ,4 union all
SELECT 15, 4 ,8 union all
SELECT 16, 3 ,3

Select T3.*
From @T T3
Join (
Select distinct T.Type , T2.Priority
from @T T
join (Select Type, Min(Priority) Priority
FROM @T
Group by Type
) T2 ON T.Type = T2.Type and T.Priority = T2.Priority
) T4 on T3.Type = T4.Type And T3.Priority = T4.Priority
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-10-18 : 13:12:34

awesome works perfectly. Thanks Tara

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:23:19

And with the new ROW_NUMBER() function in SQL Server 2005

SELECT Key, Type, Priority FROM (
SELECT Key, Type, Priority, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Priority) AS RecID FROM Table1
) AS d WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources