get maximum from each datetime values

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 maximum from each datetime values

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-03-05 : 14:33:24

hello all,

i have table - compmail in that i have compmailid, compmaildetail and compmaildate - columns

compmailid compmaildetail compmaildate
-------------------------------------------
94341 comp mailing list 2008-1-23 16:41:17.037
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108229 comp mailing sales 2008-1-27 15:13:37.207
108740 comp mailing usages 2008-1-27 16:44:17.517
108261 comp mailing stats 2008-1-27 14:42:55.780

so i want maxcompmailid for each date
so output will be like:

compmailid compmaildetail compmaildate
-------------------------------------------
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108740 comp mailing usages 2008-1-27 16:44:17.517

if i m doing:

select max(compmailid),compmaildetail,max(compmaildate) from compmail
group by compmaildetail

not giving me correct results which i want

can anyone have idea?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-05 : 14:57:01

[code]SELECT c1.compmailid,
c1.compmaildetail,
c1.compmaildate
FROM compmail c1
JOIN (SELECT MAX(compmailid) AS compmailid
FROM compmail c1
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, compmaildate), 0)) c2
ON c1.compmailid = c2.compmailid[/code]

ayu
Starting Member

43 Posts

Posted - 2008-03-05 : 15:11:54

cool!!

thanks jdaman. perfect solution.
thanks man.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 02:09:43

And if you are using SQL Server 2005

select compmailid, compmaildetail, compmaildate from (
select compmailid, compmaildetail, compmaildate, row_number() over (partition by datediff(day, '19000101', compmaildate) order by compmaildate desc) as recid
) as d where recid = 1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:01:05

and see what you can do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources