Only Return the Max Row - 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 2000 Forums

Transact-SQL (2000)

Only Return the Max Row

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-11-09 : 03:09:24

Hi, I need help
I would like to return only the row with the MAX(V.EventHistID):
Query
Select max(V.EventHistID), V.ControlID, V.ControlValue
from dbo.fcEventArchive E
left join dbo.fcEventValueArchive V
on E.EventHistID = V.EventHistID
where E.ProcessID = 351
and V.ControlID = 'FG1010NItemD'
group by V.EventHistID, V.ControlID, V.ControlValue
having V.EventHistID = MAX(V.EventHistID)

Result:
EventHisID ControlID ControlValue
1126 FG1010NItemD AAA
1124 FG1010NItemD BBB

I keep on returning both rows, they have different control value. But I oly want the max eventhistid = 1126

Please Assist!
Regards,

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 03:36:22

you want something like this...


Select V.EventHistID, V.ControlID, V.ControlValue
from dbo.fcEventArchive E 
	left join dbo.fcEventValueArchive V 
	on E.EventHistID = V.EventHistID 
	join(select max(EventHistID) as max_id,ControlID
		from dbo.fcEventValueArchive
		group by controlid) max_qry
	on max_id = V.EventHistID and max_qry.ControlID =V.ControlID
where E.ProcessID = 351 
	and V.ControlID = 'FG1010NItemD'

not sure about the logic of the inner / outer join without some sample data etc, but should be enought to give you the right idea i think..?

Em

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-11-09 : 04:37:34

Hi,

A Big Thank You for assisting me!!!

Select E.EventHistID, V.ControlID, V.ControlValue
from dbo.fcEventArchive E
left join dbo.fcEventValueArchive V
on E.EventHistID = V.EventHistID
join
(select max(v1.EventHistID) as max_id,v1.ControlID
from dbo.fcEventArchive E1
left join dbo.fcEventValueArchive V1
on E1.EventHistID = V1.EventHistID
where E1.ProcessID = 351
group by v1.controlid) max_qry

on max_id = V.EventHistID
and max_qry.ControlID =V.ControlID
where E.ProcessID = 351 and V.ControlID = 'FG1010NItemD'

Thank You

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources