Distinct Select 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

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct Select Query

Author

Topic

icesnake
Starting Member

3 Posts

Posted - 2011-07-06 : 10:31:04

id, last_name, first_name, age
1 Bryant Kobe 33
2 James Lebron 23
3 Bryant Allen 45
4 Bryant Phil 88
5 James Malik 44

How can have a result of
last_name, first_name, age
Bryant Kobe 33
James Lebron 23

Basically distinct last name then corresponding minimum age with its first name??

Thanks for any help

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-06 : 10:39:56

This seems like homework...

Here is a hint: Row_Number() Over(Partition By last_name Order By age)

Corey

I Has Returned!!

icesnake
Starting Member

3 Posts

Posted - 2011-07-06 : 11:38:20

Hi, thanks seventhnight for the reply. I already got a solution using two select queries but I'm wondering if it can be done using only 1 select? I also tried reading about the over clause but can't seem to do it in 1 select.

This is the query i used

select * from persons where age in (
select MIN(age) from persons
group by last_name)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-06 : 12:29:20

Techinically, I'd say that is one query, though it wouldn't always give you the correct results...

What if you added this to your table:
6 Gates Bill 45

Using Row_Number:

Select *
From
(
Select
*,
RowNum = Row_Number() Over(Partition By last_name Order By age)
From persons
) A
Where RowNum = 1

Corey

I Has Returned!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-06 : 12:33:30

Your query won't quite work 100% of the time. Here are a couple of ways to get the desired results:

DECLARE @Persons TABLE(id INT, last_name varchar(30), first_name varchar(30), age TINYINT)
INSERT @Persons VALUES
(1, 'Bryant', 'Kobe', 33),
(2, 'James', 'Lebron', 23),
(3, 'Bryant', 'Allen', 45),
(4, 'Bryant', 'Phil', 88),
(5, 'James', 'Malik', 44),
(6, 'James', 'Test', 33) -- Added a row to show issue

-- Original Query -- not quote right
select * 
from @persons 
where age in 
    (
        select MIN(age) 
        from @persons 
        group by last_name
    )
-- With RowNumber
SELECT 
    *
FROM
    (   
        SELECT *, ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY age ASC) AS RowNum
        FROM @Persons
    ) AS T
WHERE
    RowNum = 1   

-- With Join to derived table
select 
    P.* 
from 
    @persons  AS P
INNER JOIN    
    (
        select last_name, MIN(age) AS age
        from @persons 
        group by last_name
    ) AS T
    ON P.last_name = T.last_Name
    AND P.age = T.age

icesnake
Starting Member

3 Posts

Posted - 2011-07-07 : 05:45:18

Thanks for all the help. I understand the issue you mentioned but I'm working on a primary key so I think the MIN clause is acceptable. So even with the OVER clause, I can't make a single select query w/o joining a table. I think I'll stick with the MIN clause as it is much shorter :) Anyways, I now know about the OVER clause, it's quite useful. Thanks and much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources