get max value by datetime - in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get max value by datetime - in query

Author

Topic

kiri
Starting Member

38 Posts

Posted - 2008-02-28 : 12:03:52

i have select query :

select indxid, indxname, createddate from outmailtab
where indxname like 'update%'

indxid indxname createddate
----------------------------------------------------------------------
84627 update_sept2007 9/26/2007 10:13:46 AM 2007-09-26 10:14:11.213
84652 update_sept2007 9/26/2007 11:20:29 AM 2007-09-26 11:21:15.947
84675 update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
150326 update_Jan2008 1/22/2008 12:32:25 PM 2008-01-22 12:40:48.490
150430 update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523

i want output like max row ...

indxid indxname createddate
----------------------------------------------------------------------

84675 update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
150430 update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523

can anyone tell me how to get it?

i tried like:

select indxid, indxname, max(createddate) from outmailtab
where indxname like 'update%'
group by indxid, indxname

but still its getting me same results..i think because of datetime..date is same for different indxid but time is different for all...so can anyone help me to figure it out with datetime function..

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 12:10:01

Try:-

SELECT t1.indxid, t1.indxname, t1.createddate
FROM outmailtab t1
INNER JOIN (select indxname, max(createddate) as maxdate
from outmailtab
where indxname like 'update%'
group by indxname) t2
ON t2.indxname=t1.indxname
AND t2.maxdate=t1.createddate

kiri
Starting Member

38 Posts

Posted - 2008-02-28 : 12:35:02

quote:

Originally posted by visakh16

Try:-

SELECT t1.indxid, t1.indxname, t1.createddate
FROM outmailtab t1
INNER JOIN (select indxname, max(createddate) as maxdate
from outmailtab
where indxname like 'update%'
group by indxname) t2
ON t2.indxname=t1.indxname
AND t2.maxdate=t1.createddate

Thanks visakh16 but still getting same results..

i think i ahve to use Datepart function of hh:mm:ss from createddate and which is greater that row i want..but still confused how to get that.

thanks for ur help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 12:46:05

isnt createddate a datetime field?

kiri
Starting Member

38 Posts

Posted - 2008-02-28 : 12:54:21

it is datetime field..but u know when i see all values for createddate field in outmailtab table is like: 2/23/2008 4:31:20 AM inserted in table...whole column...

SELECT t1.indxid, t1.indxname, t1.createddate, CONVERT(CHAR(8), t1.createddate,114) as time
FROM outmailtab t1
INNER JOIN (select indxname, max(createddate) as maxdate
from outmailtab
where indxname like 'update%'
group by indxname) t2
ON t2.indxname=t1.indxname
AND t2.maxdate=t1.createddate

so i got different field but still not max row
and if i m doing - max(CONVERT(CHAR(8), t1.createddate,114)) as time
still same...

kiri
Starting Member

38 Posts

Posted - 2008-02-28 : 13:39:12

i have done like this so far:

select indxid, indxname, createddate, CONVERT(CHAR(10), createddate,101) as date, CONVERT(CHAR(8), createddate,114) as time from outmailtab
where indxname like 'update

i got output like this:

indxid indxname createddate date time
----------------------------------------------------------------------
108229 update_Nov2007 11/27/2007 3:13:03 PM 11/27/2007 15:13:37
108261 update_Nov2007 11/27/2007 4:43:35 PM 11/27/2007 16:44:17

now i want only one row which is second max time from same date..

can anyone help me to write condition on that.

thanks.

kiri
Starting Member

38 Posts

Posted - 2008-02-28 : 14:17:45

any help would be appreciated..pl help to figure it out this query

thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:56:50

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 05:54:03

select indxid, indxname, createddate, createddate from outmailtab t
where indxname like 'update and createddate=(select max(createddate) from outmailtab where indxid=t.indxid)

Madhivanan

Failing to plan is Planning to fail

kiri
Starting Member

38 Posts

Posted - 2008-02-29 : 08:49:25

thanks madhivanan and peso..but still i m getting same results...i want max datetime row for each date... and i found that max datetime row has mas indxid too...so i also tried to do maxindxid if i can ge that row...but still same results..

i want like: exxample
1/1/2008 10:00:00 AM
1/1/2008 15:30:15 PM

then i want maxdatetime row...secondone which has maxtime for that date.1/1/2008 15:30:15 PM row will be in output..
same way for each dateandtime....

thanks a lot!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 09:08:18

[code]SELECT indxid,
indxname,
createddate
from (
SELECT indxid,
indxname,
createddate,
row_number () over (partition by indxid order by createddate desc) as recid
from outmailtab
where indxname like 'update%'
) AS d
where recid = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

kiri
Starting Member

38 Posts

Posted - 2008-02-29 : 09:13:50

peso, it gives me error like:

'row_number' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 09:23:45

It shouldn't. You have posted in a Microsoft SQL Server 2005 forum.
Or... Change Compatibility Level to 90 for your current database.

E 12°55'05.25"
N 56°04'39.16"

kiri
Starting Member

38 Posts