Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
--
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.