Select Max date and time from two columns

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 2008 Forums

Transact-SQL (2008)

Select Max date and time from two columns

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-09-25 : 18:41:06

I need to select a row with Max Date and time. Date and time are placed in two columns.

Here is what I have:

select Max(date1+Time1), Id from table1 (this is returning more than one row.


CREATE TABLE TABLE1(
	[ID] INT,
	[DATE1] [datetime] NOT NULL,
	[TIME1] [datetime] NULL,
	[WK] INT
	)
	
		
	
INSERT INTO Table1 (ID,DATE1,TIME1, WK) VALUES ('1111','2013-09-25', '2000-01-01 10:49:00.000', '1');
INSERT INTO Table1 (ID,DATE1,TIME1, WK) VALUES ('1111','2013-09-25', '2000-01-01 10:15:00.000', '2');
INSERT INTO Table1 (ID,DATE1,TIME1, WK) VALUES ('1112','2013-09-25', '2000-01-01 11:11:00.000', '1');
INSERT INTO Table1 (ID,DATE1,TIME1, WK) VALUES ('1112','2013-09-25', '2000-01-01 10:49:00.000', '2');



Desire outcome

ID            DATE1             TIME1                     WK
1111         2013-09-25     2000-01-01 10:49:00.000        1
1112         2013-09-25     2000-01-01 11:11:00.000        1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-25 : 23:00:16

[code]
select *
from
(
select *, rn = row_number() over (partition by ID order by DATE1 desc, TIME1 desc)
from TABLE1
) d
where rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-26 : 01:14:40

SELECT T1.* FROM TABLE1 T1
JOIN (SELECT ID, MAX(DATE1+TIME1) MaxDate FROM TABLE1 GROUP BY ID ) T2
ON T1.ID = T2.ID AND T1.DATE1+T1.TIME1 = MaxDate

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-26 : 02:55:44

[code]SELECT TOP(1) *
FROM dbo.Table1
ORDER BY Date1 DESC, Time1 DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources