SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select Max date and time from two columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emyk
Yak Posting Veteran

57 Posts

Posted - 09/25/2013 :  18:41:06  Show Profile  Reply with Quote
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





Edited by - emyk on 09/25/2013 21:56:59

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 09/25/2013 :  23:00:16  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 09/26/2013 :  01:14:40  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/26/2013 :  02:55:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT TOP(1) *
FROM          dbo.Table1
ORDER BY      Date1 DESC, Time1 DESC;


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

Edited by - SwePeso on 09/26/2013 02:56:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000