Need Max date?

Here is what I have so far:
SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
SPEEDLOCATIONS.[CROSS]
FROM SPEEDLOCATIONS INNER JOIN
SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
WHERE (SPEEDLOCATIONS.CITY = 'HOU')
ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
DavidCould you provide DDL and sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Here is what I have so far:
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
> --
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
> Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
> Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
> Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
>
> I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
>
>
> David
>|||CREATE TABLE [SPEED] (
[CITY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID] [numeric](7, 0) NOT NULL ,
[SPEEDMPH] [numeric](5, 0) NOT NULL ,
[DATETIME] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SPEEDLOCATIONS] (
[OBJECTID] [int] NULL ,
[CITY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] NULL ,
[HWY] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HWYDIR] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CROSS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LANEID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col009] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col010] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col011] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col012] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col013] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col014] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eRk56nbHFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Could you provide DDL and sample data?
> http://www.aspfaq.com/5006
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>|||Here's one possible way (untested):
SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
SPEEDLOCATIONS.[CROSS]
FROM SPEEDLOCATIONS INNER JOIN
SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
WHERE (SPEEDLOCATIONS.CITY = 'HOU')
AND SPEED.DATETIME =
(SELECT MAX(S1.DATETIME)
FROM SPEED S1
JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
WHERE SL1.HWY = SPEEDLOCATIONS.HWY
AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Here is what I have so far:
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
> --
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Ella
> Beltway 8-Nort 50 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 52 3/9/2004 12:47:12 PM Ella
> Beltway 8-Nort 60 3/9/2004 11:45:56 AM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Beltway 8-Nort 70 3/9/2004 1:36:12 PM Fairbanks at US-290
> Beltway 8-Nort 39 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 58 3/9/2004 12:47:12 PM Fairbanks at US-290
> Beltway 8-Nort 45 3/9/2004 11:45:56 AM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 1:42:08 PM Aldine-Bender
> Hardy Tollroad 61 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 66 3/9/2004 12:47:12 PM Aldine-Bender
> Hardy Tollroad 70 3/9/2004 12:46:17 PM Aldine-Bender
>
> I just want to select the latest date for each SPEEDLOCATIONS/.[CROSS]
> Beltway 8-Nort 70 3/9/2004 1:45:45 PM Ella
> Beltway 8-Nort 70 3/9/2004 1:42:20 PM Fairbanks at US-290
> Hardy Tollroad 70 3/9/2004 1:42:14 PM Aldine-Bender
>
>
> David
>|||You can try using max(SPEED.DATETIME) and group by SPEEDLOCATIONS.HWY
"Adam Machanic" wrote:

> Here's one possible way (untested):
>
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> AND SPEED.DATETIME =
> (SELECT MAX(S1.DATETIME)
> FROM SPEED S1
> JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
> WHERE SL1.HWY = SPEEDLOCATIONS.HWY
> AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>
>|||I get no data back?
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OTzFcwbHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> Here's one possible way (untested):
>
> SELECT DISTINCT SPEEDLOCATIONS.HWY, SPEED.SPEEDMPH, SPEED.DATETIME,
> SPEEDLOCATIONS.[CROSS]
> FROM SPEEDLOCATIONS INNER JOIN
> SPEED ON SPEEDLOCATIONS.ID = SPEED.ID
> WHERE (SPEEDLOCATIONS.CITY = 'HOU')
> AND SPEED.DATETIME =
> (SELECT MAX(S1.DATETIME)
> FROM SPEED S1
> JOIN SPEEDLOCATIONS SL1 ON S1.ID = S.ID
> WHERE SL1.HWY = SPEEDLOCATIONS.HWY
> AND SL1.[CROSS] = SPEEDLOCATIONS.[CROSS])
> ORDER BY SPEEDLOCATIONS.HWY, SPEEDLOCATIONS.[CROSS], SPEED.DATETIME DESC
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message
> news:ORxyqgbHFHA.4060@.TK2MSFTNGP14.phx.gbl...
>|||"DaveF" <dfetrow@.geodecisions.com> wrote in message
news:OgZfhTcHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> I get no data back?
?
You posted no sample data, so I was unable to test.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--