Group by question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by question

Author

Topic

mxmiked
Starting Member

3 Posts

Posted - 2009-09-01 : 09:58:47

I have a table like this.

USE [database1]
GO
/****** Object: Table [dbo].[info_table] Script Date: 09/01/2009 09:44:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[info_table](
[car_make_id] [smallint] NOT NULL,
[driver_id] [int] NOT NULL,
[last_used] [datetime] NULL CONSTRAINT
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

A driver can have multiple cars so data can look like this.

car_make_id,driver_id,last_used
281,4820,'2008-05-18 01:42:22'
283,4820,'2008-02-16 15:51:39'
281,4820,'2008-12-14 23:29:42'
281,5920,'2008-08-22 16:46:59'
283,5920,'2008-04-17 03:23:15'
281,5920,'2008-10-11 09:16:38'

I need to determine which drivers, drove which car, last.

My *not working* query so far.

SELECT driver_id,MAX(last_used) AS last_used
FROM database1.dbo.info_table
GROUP BY driver_id

This is not a bad start but I don't know which car was used.

OBVIOUSLY...

SELECT driver_id,car_make_id,MAX(last_used) AS last_used
FROM database1.dbo.info_table
GROUP BY driver_id,car_make_id

Will not work because it will return everything.

Any ideas on how to do this?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 10:04:08

[code]SELECT a.car_make_id,a.driver_id,a.last_used FROM
(
SELECT car_make_id,driver_id,last_used, row_number () over (partition by driver_id order by last_used desc) as seq FROM info_table
) a
WHERE a.seq = 1[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 10:05:54

Try this

select t1.* from database1.dbo.info_table as t1 inner join
(
SELECT driver_id,MAX(last_used) AS last_used
FROM database1.dbo.info_table
GROUP BY driver_id
) as t2 on t1.driver_id=t2.driver_id and t1.last_used=t2.last_used

Madhivanan

Failing to plan is Planning to fail

mxmiked
Starting Member

3 Posts

Posted - 2009-09-01 : 10:07:41

quote:

Originally posted by vijayisonly

SELECT a.car_make_id,a.driver_id,a.last_used FROM
(
SELECT car_make_id,driver_id,last_used, row_number () over (partition by driver_id order by last_used desc) as seq FROM info_table
) A
WHERE A.seq = 1

Wow, that looks amazing, will try it now.

mxmiked
Starting Member

3 Posts

Posted - 2009-09-01 : 10:15:03

quote:

Originally posted by vijayisonly

SELECT a.car_make_id,a.driver_id,a.last_used FROM
(
SELECT car_make_id,driver_id,last_used, row_number () over (partition by driver_id order by last_used desc) as seq FROM info_table
) a
WHERE a.seq = 1

Worked perfectly! This is awesome!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 10:21:45

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 10:33:13

Ok. To know more about row_number, refer
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