Please help with this tough SQL query

[Roman]

I've been trying this one for 2-3 hours and can't figure it out. I'de
appreciate any help or pointers in the right direction. Thanks.

Query
I need the query to return me all the lottery names and results that
have the latest date in the database for that particular game and for
the state [AZ]. So the return data from the data below data would be:

Result:
--------------------------
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Example Table "Lottery":
----------------------------------------------------
State|Game | Date | Results

AZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-07-29 2-23-62-77
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

[Simon Hayes]

"Roman" <ro******@verizon.net> wrote in message
news:fa**************************@posting.google.c om...

I've been trying this one for 2-3 hours and can't figure it out. I'de
appreciate any help or pointers in the right direction. Thanks.

Query
I need the query to return me all the lottery names and results that
have the latest date in the database for that particular game and for
the state [AZ]. So the return data from the data below data would be:

Result:
--------------------------
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Example Table "Lottery":
----------------------------------------------------
State|Game | Date | Results

AZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-07-29 2-23-62-77
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Here is one possible solution:

select l.state, l.game, l.gamedate, l.results
from Lottery l
join (
select state, game, max(gamedate) as gamedate
from Lottery
group by state, game
) dt
on l.state = dt.state
and l.game = dt.game
and l.gamedate = dt.gamedate

For future reference, you will usually get a better answer if you post your
table information and data in the form of a CREATE TABLE statement plus
INSERTs for the data. That way, other people can simply copy, paste and run
your code in Query Analyzer, and you also avoid any confusion about data
types, constraints etc.

Simon

[Roman]

This didnt quite work for me, but I finanally figured out a working
solution. Thanks.

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...

"Roman" <ro******@verizon.net> wrote in message
news:fa**************************@posting.google.c om...

I've been trying this one for 2-3 hours and can't figure it out. I'de
appreciate any help or pointers in the right direction. Thanks.

Query
I need the query to return me all the lottery names and results that
have the latest date in the database for that particular game and for
the state [AZ]. So the return data from the data below data would be:

Result:
--------------------------
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Example Table "Lottery":
----------------------------------------------------
State|Game | Date | Results

AZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-07-29 2-23-62-77
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Here is one possible solution:

select l.state, l.game, l.gamedate, l.results
from Lottery l
join (
select state, game, max(gamedate) as gamedate
from Lottery
group by state, game
) dt
on l.state = dt.state
and l.game = dt.game
and l.gamedate = dt.gamedate

For future reference, you will usually get a better answer if you post your
table information and data in the form of a CREATE TABLE statement plus
INSERTs for the data. That way, other people can simply copy, paste and run
your code in Query Analyzer, and you also avoid any confusion about data
types, constraints etc.

Simon

[Ross Presser]

On 9 Aug 2004 10:12:15 -0700, Roman wrote:

I've been trying this one for 2-3 hours and can't figure it out. I'de
appreciate any help or pointers in the right direction. Thanks.

Query
I need the query to return me all the lottery names and results that
have the latest date in the database for that particular game and for
the state [AZ]. So the return data from the data below data would be:

Result:
--------------------------
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Example Table "Lottery":
----------------------------------------------------
State|Game | Date | Results

AZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18
AZ Atlantic PayDay 2004-07-29 2-23-62-77
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3
AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

Inferred DDL statement:

CREATE TABLE "Lottery" (
"State" char(2),
"Game" char(30),
"Date" datetime,
"Results" char(255),
CONSTRAINT pk_Lottery PRIMARY KEY (State,Game,Date)
)
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic 6/49', '2004-08-04', '5-16-17-26-38-44 46')
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic 6/49', '2004-08-07', '3-6-8-12-19-24 18')
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic PayDay', '2004-07-29', '2-23-62-77')
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic PayDay', '2004-08-05', '15-51-59-75')
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic Tag', '2004-08-04', '5-8-9-1-2-3')
INSERT INTO Lottery (State,Game,Date,Results)
VALUES ('AZ', 'Atlantic Tag', '2004-08-08', '4-6-1-6-7-6')

Here's the query you need:

SELECT A.State, A.Game, A.[Date], a.Results
From Lottery A
INNER JOIN (
SELECT b1.State, b1.Game, Max(b1.[Date]) "MaxDate"
FROM Lottery B1
GROUP BY b1.State, b1.Game ) B
ON A.State = B.State
AND A.Game = B.Game
AND A.Date = B.MaxDate
WHERE A.State = 'AZ'

Results:

AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6
AZ Atlantic PayDay 2004-08-05 15-51-59-75
AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18

(3 row(s) affected)