Quick, simple problem - 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

Quick, simple problem

Author

Topic

johsulli
Starting Member

1 Post

Posted - 2008-09-23 : 15:50:35

If anyone can help, this is very basic, it's appreciated.

Let’s say I have the following table:

Event_id Oper_Name Action
1 James Make Account
2 James Make Account
3 James Fund Account
4 James Create User
5 James Write Data
1 John Write Data
2 John Create User
3 John Fund Account

I want to get the Action where the Operator ID is James and the Event ID is the highest (max). In this case, I want the row:

5 James Write Data

Currently I perform two statements:

1.) Select max(event_id) from table where oper_name = James.
2.) Select Action from table where oper_name = James AND event_id = the max I retrieved.

I get bad syntax if I try to say AND event_id = max(event_id). Is there a way to do this with one statement for the sake of performance?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-23 : 16:05:00

[code]
SELECT MAX(Event_id) AS Event_id, Oper_Name
FROM YourTable
GROUP BY Oper_Name

To get the other column(s), use a derived table to the above query:
SELECT dt.Event_id, dt.Oper_Name, t.Action
FROM YourTable t
INNER JOIN
(
SELECT MAX(Event_id) AS Event_id, Oper_Name
FROM YourTable
GROUP BY Oper_Name
) dt
ON t.Event_id = dt.Event_id AND t.Oper_Name = dt.Oper_Name
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-24 : 01:26:31

If you are using Sqlserver 2005 then use the below

declare @Demo table ( Event_id int ,Oper_Name Varchar(30),Action Varchar(256))
insert into @demo
select 1, 'James', 'Make Account' union all
select 2, 'James', 'Make Account' union all
select 3, 'James', 'Fund Account' union all
select 4, 'James', 'Create User' union all
select 5, 'James', 'Write Data' union all
select 1, 'John', 'Write Data' union all
select 2, 'John', 'Create User' union all
select 3, 'John', 'Fund Account'

--select * from @demo

select d.Event_Id,d.Oper_Name, d.Action
from ( select row_number() over ( partition by oper_name order by event_id desc ) as sno , event_id,oper_name,action from @demo ) d
where d.sno = 1 and d.oper_name = 'James'

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-28 : 19:53:12

or

Select O.Event_id,O.Oper_Name,O.Action
from @demo O
where O.event_id = (Select max(I.event_id) from @demo I
where I.oper_name = O.oper_name)
and O.oper_name = 'James'

khasim76
Starting Member

35 Posts

Posted - 2008-09-29 : 00:56:48

COULD SOMEBODY EXPLAIN CODE RETURN BY RAKY .ITS VERY INTERESTING...PLZ ESPECIALLY PARTION BY THAT PART. THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 01:19:28

quote:
Originally posted by khasim76

COULD SOMEBODY EXPLAIN CODE RETURN BY RAKY .ITS VERY INTERESTING...PLZ ESPECIALLY PARTION BY THAT PART. THANKS

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

also can you please turn your capslock off?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-29 : 01:19:34

Read row_number() function in sql server 2005 Books Online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources