aggregate sql question

[Neal B. Scott]

Given this table:
CREATE TABLE #T_PEOPLE (
SSN char (9),
BIRTHDAY datetime
)
insert #T_PEOPLE(ssn, birthday) values('123456789', '1/1/60')
insert #T_PEOPLE(ssn, birthday) values('111223333', '1/1/50')

Which query is faster to find the ssn of the older person?
This query:
select top 1 SSN
from #T_PEOPLE
order by birthday
or this one:
select SSN
from #T_PEOPLE
where birthday=
(select min(birthday) from #T_PEOPLE)

[Madhivanan]

I prefer first as it does not require grouping and subquery

Madhivanan

[David Portas]

Those two queries are not equivalent so a performance comparison is not
necessarily very useful. The TOP query will return at most, one row. If
there are multiple people with the same birthday for the minimum date
then you will get one arbitrary, unknown row. Unpredictable results are
generally bad news so if you use that version I recommend you use the
WITH TIES option.

Also, I notice you have made Birthday nullable. The TOP result will
return a NULL if any exists, so you will probably want to add WHERE IS
NOT NULL. The following is nearly equivalent to the MIN() query:

SELECT TOP 1 WITH TIES ssn
FROM #T_people
WHERE birthday IS NOT NULL
ORDER BY birthday

Except that this will return an empty set rather than a NULL if the set
Birthday IS NOT NULL is empty. Top queries are harder than they look!

As to which is quicker. Performance generalizations are really no
substitute for doing the testing yourself with your structure,
constraints, data and indexes - factors which I know nothing about.

--
David Portas
SQL Server MVP
--