Max or Top or Something else? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Max or Top or Something else?

Author

Topic

gtFlue
Starting Member

8 Posts

Posted - 2012-01-31 : 08:19:05

I'm shifting gears a little bit from trying to filter out duplicates in a join to maybe limiting the number of rows to match in the first place.

Say I have a table with 100 rows. There are 4 names and 25 service dates for each name. I would like to return the most recent date for each name.

SELECT Top 1 * from myTable
Order by ServiceDateTime desc

Only returns one row, the most recent from all rows.

Since my ServiceDateTime format is YYYY-MM-DD HH:MM:SS.sss;

SELECT MAX(ServideDateTime) from myTable Group by SerivceDateTime
Order by ServiceDateTime

is returning all rows.

I seem to be missing the boat here. Any direction would be most appreciative.

Thanks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-31 : 08:22:02

select name, max(servicedatetime) from mytable group by Name

Duane.

gtFlue
Starting Member

8 Posts

Posted - 2012-01-31 : 08:54:15

Figured I was missing the boat!
Next, I'll plug it into a subquery of my Join and see what happens.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 13:24:31

quote:
Originally posted by gtFlue

Figured I was missing the boat!
Next, I'll plug it into a subquery of my Join and see what happens.

Thanks!

you'll need a join with your main table like this


SELECT t.*
FROM mytable t
join ( select name, max(servicedatetime) as latest from mytable group by Name)t1
on t1.name = t.name
and t1.latest = t.servicedatetime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gtFlue
Starting Member

8 Posts

Posted - 2012-02-01 : 13:02:44

Thanks.

I'll give that a go and see what happens.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:10:55

welcome
let us know if you face any issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gtFlue
Starting Member

8 Posts

Posted - 2012-02-02 : 10:16:57

quote:
Originally posted by visakh16

quote:
Originally posted by gtFlue

Figured I was missing the boat!
Next, I'll plug it into a subquery of my Join and see what happens.

Thanks!

you'll need a join with your main table like this

SELECT t.*
FROM mytable t
join ( select name, max(servicedatetime) as latest from mytable group by Name)t1
on t1.name = t.name
and t1.latest = t.servicedatetime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Fantastic bit of information there! Takes my results down from 1.5M to 4500 and change.

Thanks Much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:22:33

ok
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources