Assistance needed in Query - not giving up, but.... 2

[Deadline]

Hello,
Please assist me in framing the query as explained below.

NAME                COUNTRY  CITY             DATE
Dave Murphy         USA      Dallas           01/01/2002
Dave Murphy         USA      Washington DC    01/02/2002
Dave Murphy         USA      Miami            01/03/2002 
Dave Murphy         USA      New York City    01/04/2002
Dave Murphy         UK       London           02/01/2002
Dave Murphy         UK       Manchester       02/01/2002
Sony Aibo           UK       Manchester       02/01/2002

from the above records, I want to Get the Record with the earliest date alone, that is, the expected result set is like this

NAME                COUNTRY  CITY             DATE
Dave Murphy         USA      Dallas           01/01/2002
Dave Murphy         UK       London           02/01/2002
Sony Aibo           UK       Manchester       02/01/2002

In case the dates are equal for a country, the first one needs to be considered.

How to do this ? Kindly help. Thank you,
RR.
__________________________________
The best is yet to come.

 

[tlbroadbent]

Try this. Note that the minimum country will be selected rather than the "first" country. T-SQL doesn't have a First function and order is not guaranteed. If the table has a unique ID, you could use that in place of country in the sub-query and join criteria.

Select [Name], Country, City, [Date]
From TableName t
Inner Join
(Select [Name], Country=Min(Country), Date=Min([Date]
From TableName
Group By [Name]) q
On t.[Name]=q.[Name]
And t.Country=q.Country
And t.[Date]=q.[Date] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Andel]

select a.name,
a.country,
city = (select top 1 city from TableName where name = a.name and country = a.country),
date = convert(varchar(20), min(a.date),110)
from TableName a
group by name, country
order by name, date Andel
andel@barroga.net

http://andyb.8m.com