max date row

Question

max date row

rpatil22

Ten Centuries

Points: 1371
More actions
March 26, 2009 at 10:41 am

#72599

I have a data in a table , how to select max date's each home , location and status.
Date Home Location Status
2009-03-22 03:15:01.417 New East 2
2009-03-15 01:16:01.242 New East 3
2009-03-25 03:26:01.122 New East 5
2009-03-21 04:12:01.307 Old North 0
2009-03-25 01:26:01.102 Old North 0
2009-03-26 03:16:01.111 Old North 1

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

just a simple grouping is what i would use;

note how i put your data into CREATE and INSERT INTO so that others could use it as sample data? that's a best practice, so others can really help you; try and do the same in the future

CREATE TABLE #TMP(Date datetime, Home varchar(30), Location varchar(30),Status int )

INSERT INTO #TMP(Date, Home, Location, Status)

SELECT '2009-03-22 03:15:01.417','New','East',2 UNION ALL

SELECT '2009-03-15 01:16:01.242','New','East',3 UNION ALL

SELECT '2009-03-25 03:26:01.122','New','East',5 UNION ALL

SELECT '2009-03-21 04:12:01.307','Old','North',0 UNION ALL

SELECT '2009-03-25 01:26:01.102','Old','North',0 UNION ALL

SELECT '2009-03-26 03:16:01.111','Old','North',1

select max([date]) as TheDate,Home, Location, Status

from #tmp

group by Home, Location, Status

Results:

TheDateHomeLocationStatus

2009-03-22 03:15:01.417NewEast2

2009-03-15 01:16:01.243NewEast3

2009-03-25 03:26:01.123NewEast5

2009-03-25 01:26:01.103OldNorth0

2009-03-26 03:16:01.110OldNorth1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rpatil22 Ten Centuries Points: 1371 More actions · Answer 2

rpatil22

Ten Centuries

Points: 1371

March 26, 2009 at 11:18 am

#966881

i want max date only for every home

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lowell has given you the principle you need to figure out what needs to be done. May I suggest you read up on GROUP BY in Books Onlie? If you still have questions after that, let us know.

Lowell SSC Guru Points: 323518 More actions · Answer 4

you said how to select max date's each home , location and status.

like lynn said, all the tools are there...run this, test it, and drop the columns you don't need.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!