Group By, Distinct help 2

[obrienk]

Hi,

Can anybody help me out with this query? Given the table below, I want to find the Surname of the youngest person called John:

forename   |   surname   |  dob
---------------------------------------
John       |   Smith     |  01/05/1970
Mary       |   Brun      |  23/11/1974
Frank      |   Smith     |  05/05/1983
John       |   Jones     |  12/08/1967

I can almost get there using this:

select distinct(forename), max(dob) 
from people
WHERE
forename = 'John'
GROUP BY forename

which selects the correct row, but I can't work out how to get it to give me the surname field.

Many Thanks,
K.

 

[PHV]

Have you tried this ?

SELECT * FROM people 
 WHERE forename='John'
   AND dob=(SELECT MAX(dob) FROM people
             WHERE forename='John')

Hope This Help
PH.

 

[nigelrivett]

select top 1 *
from tbl
where name = 'john'
order by dob desc


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

 

[Lekar]

This might work:
[tt]
Select *
From People
Where Dob = (Select Max(Dob)
From People
Where ForeName = 'John')
And ForeName = 'John';
[/tt]


Lenin.

 

[obrienk]

Thanks guys,

The inner join method is simpler & it does the trick.

One thing though - how efficient is this? I need to do it a few hundred times on a large table (27 million records). It's indexed & currently works very quickly, but I'm just wondering if you can see a problem with this in the long run?

Thanks again,
K.

 

[r937]

i didn't see an inner join method

i saw the same uncorrelated subquery method twice (and some non-standard method that might work in some proprietary database if you happen to have it), but no inner join method

the inner join method would be:

[tt]select t1.forename
, t1.surname
, t1.dob
from people t1
inner
join people t2
on t1.forename
= t2.forename
where forename = 'John'
group
by t1.forename
, t1.surname
, t1.dob
having t1.dob = max(t2.dob)[/tt]

but i have a feeling it will not perform well

i suspect a correlated subquery might run any faster than the uncorrelated, and hey, if you already have a large table and can run tests, i'd be curious how it stacks up...

[tt]select *
from people AA
where forename = 'John'
and dob =
( select max(dob)
from people
where forename = AA.forename )[/tt]

rudy

http://r937.com/

SQL Consulting

 

[r937]

oh, and if you need to do several hundred of these, you will probably find the correlated subquery easier, as you would only have to enter the several hundred names once, not twice



rudy

http://r937.com/

SQL Consulting

 

[obrienk]

Thanks,

I'll give it a shot.

K.