max date

Question

max date

znkin

Old Hand

Points: 382
More actions
March 15, 2005 at 11:47 pm

#92633

In a table I have 2 columns which are 'actiondatetime','actionstatus'. I am trying to generate a query which will return me a row with
the max date from 'actiondatetime' and its corresponding 'actionstatus' value
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I assume you have some column that is unique. For the purposes of the eg, I will call that column ID. Also calling the table tbl

SELECT actionstatus, actiondatetime FROM tbl INNER JOIN

(SELECT id, MAX(actiondatetime) AS MaxActionDateTime FROM tbl GROUP BY actiondatetime) MaxDate ON tbl.ID=MaxDate.ID AND tbl.actiondatetime=MaxDate.MaxActionDateTime

Not tested, but should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

znkin Old Hand Points: 382 More actions · Answer 2

Thanks but its still giving an error

Column 'tbl.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Eeep, sorry.

Remove all references to id and remove the group by in the inner query. I was thinking of somethng else.

SELECT actionstatus, actiondatetime FROM tbl INNER JOIN

(SELECT MAX(actiondatetime) AS MaxActionDateTime FROM tbl) MaxDate ON tbl.actiondatetime=MaxDate.MaxActionDateTime

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

What about:

select

o.customerID

, o.orderdate

from

northwind..orders o

where

o.orderdate=

(select

max(o1.orderdate)

from

northwind..orders o1

where

o.customerID = o1.customerID)

group by

o.customerID

,o.orderdate

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 5

Maybe I'm missing something, but won't "SELECT TOP 1 actiondatetime, actionstatus FROM #tmp ORDER BY actiondatetime DESC" do the trick? The following yields "2005-03-03, Action 3" as the result... Steve

CREATE TABLE #tmp(actiondatetime datetime, actionstatus varchar(20))

INSERT INTO #tmp VALUES ('1/1/2005', 'Action 1')

INSERT INTO #tmp VALUES ('2/2/2005', 'Action 2')

INSERT INTO #tmp VALUES ('3/3/2005', 'Action 3')

INSERT INTO #tmp VALUES ('2/12/2005', 'Action 4')

INSERT INTO #tmp VALUES ('1/13/2005', 'Action 5')

SELECT TOP 1 actiondatetime, actionstatus FROM #tmp ORDER BY actiondatetime DESC

DROP TABLE #tmp