Help with this grouped simple query!

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

Help with this grouped simple query!

Author

Topic

byle
Starting Member

6 Posts

Posted - 2011-07-07 : 09:40:50

Hello,

I'm trying to generate a query for the following table:


ID   NAME   AGE
1    John   24
2    Mark   20
3    John   30
4    James  15

My query has to group by Name column in order to get the lowest age for each name. SO I do that:


SELECT NAME, MIN(AGE)
FROM MyTable
GROUP BY NAME

All correct. The problem is that I also want to have the Id of each row. My result should be:


ID   NAME   AGE
1    John   24
2    Mark   20
4    James  15

I know I could do that like this:


SELECT A.*
FROM myTABLE A
JOIN 
( SELECT NAME AS NAME, 
  MIN(AGE) AS AGE
  FROM MyTable
  GROUP BY NAME
) B ON A.NAME = B.NAME AND A.AGE = B.AGE

But In my case its not efficient beacause there are a lot of records.
Is there any other way to get the Id of the result of MIN(AGE)???

Thankyou very much!!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 09:46:49

select *
from
(
select *, seq = row_number() over (partition by id order by age)
) a
where seq == 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

byle
Starting Member

6 Posts

Posted - 2011-07-07 : 10:10:40

Wow!! So easy!! lol
Thankyou very much nigelrivett!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-08 : 04:12:28

quote:
Originally posted by nigelrivett

select *
from
(
select *, seq = row_number() over (partition by id order by age)
) a
where seq == 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Effect of front end syntax?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources