top 1 record for each companyid

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

SQL Server Development (2000)

top 1 record for each companyid

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2006-07-16 : 18:55:33

company table (companyID int, name varchar(50))
log table (logID int, companyID int, createdOn datetime)

i want to get the top 1 record from the log table for each company ordered by createdon desc, how do i do this without using temp tables?

for example:
logID | companyID | createdOn
11 | 1 | 17/07/06
23 | 4 | 17/07/06
43 | 5 | 17/07/06

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-16 : 19:01:05

This assumes that companyID, createdOn is unique in the logtable:

select l.logID, l.companyID, l.createdOn
from logTable l
join
(select companyID, max(createdOn) as maxCreatedOn) a
on l.companyID = a.companyID and l.createdOn = a.maxCreatedOn

rockmoose

matrixr
Starting Member

26 Posts

Posted - 2006-07-16 : 19:32:09

not unique, this is what i have now

select l.logID, l.companyID, l.createdOn
from log l
inner join (select companyID, max(createdOn) as maxCreatedOn from log group by companyID) a on a.companyID = l.companyID and a.maxCreatedOn = l.createdOn

works fine except it returns duplicate rows
logID | companyID | createdOn
11 | 1 | 17/07/06
12 | 1 | 17/07/06
...etc

any other ideas?

matrixr
Starting Member

26 Posts

Posted - 2006-07-16 : 19:37:25

... inner join (select max(logID) as logID, companyID, max(createdOn) as maxCreatedOn from Log group by companyID) a on l.logID = a.logID and l.companyID = a.companyID and l.createdOn = a.maxCreatedOn ...

works

thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-16 : 20:06:16

I think you are interested in the last createdOn for each company, the logID in itself is uninteresting.

If you join the derived table directly to the Company table I think you get what you want.

select c.companyID, c.name, a.maxCreatedOn
from companyTable c
join
(select companyID, max(createdOn) as maxCreatedOn group by companyID) a
on c.companyID = a.companyID

???

rockmoose

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 12:13:46

Or this...

--data
set dateformat dmy
declare @log table (logID int, companyID int, createdOn datetime)
insert @log
          select 11, 1, '17/07/06'
union all select 12, 1, '17/07/06'
union all select 13, 1, '14/07/06'
union all select 21, 4, '15/07/06'
union all select 22, 4, '16/07/06'
union all select 23, 4, '17/07/06'
union all select 43, 5, '17/07/06'
union all select 44, 5, '18/07/06'

--calculation
select * from @log a
where logID in
    (select top 1 logID from @log
    where companyID = a.companyID
    order by createdOn desc, logID desc)

/*results
logID       companyID   createdOn                                              
----------- ----------- ------------------------------------------------------ 
12          1           2006-07-17 00:00:00.000
23          4           2006-07-17 00:00:00.000
44          5           2006-07-18 00:00:00.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-17 : 13:02:58

Or just...

select * from @log a
where	logID = (	select	max(b.logID)
			from	@log b
			where	b.createdOn = (	select	max(createdOn)
							from	@log b
							where	a.companyID = b.companyID)
				and a.companyID = b.companyID)

but plain ol' sql is ok...

select max(l.logID) as logID, l.companyID, l.createdOn
from @log l
inner join (select companyID, max(createdOn) as maxCreatedOn from @log group by companyID) a
	on a.companyID = l.companyID and a.maxCreatedOn = l.createdOn
group by l.companyID, l.createdOn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-22 : 06:21:08

Also refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.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