an SQL nightmare of sort orders and rownums

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

an SQL nightmare of sort orders and rownums

Author

Topic

Out_of_my_Depth
Starting Member

1 Post

Posted - 2009-06-09 : 10:26:30

Hi,

I am trying to create a query which will bring back the highest two numbers in a column for every single row of relevant information.

E.g

Table structure

Table 1
PROCESS_INST_ID - unique

Table 2
ACTIVITY_INST_ID - unique
ACTIVITY_ID - relates to Table 3
PROCESS_INST_ID - relates to table 1

Table 3
ACTIVITY_ID - unique
ACTIVITY_NAME

so table has records like

Table 1
PROCESS_INST_ID
100

Table 2
ACTIVITY_INST_ID ACTIVITY_ID PROCESS_INST_ID
100 3 100
101 1 100
102 2 100
103 4 100

Table 3
ACTIVITY_ID ACTIVITY_NAME
1 Act 1
2 Act 2
3 Act 3
4 Act 4

So, I want to return the activities associated with ACTIVITY_INST_ID 102 and 103, being last two "actions" for process_inst_id 100 (activity_inst_id is incrementing number so always want the last two for any Process_inst_id)

so something like
PROCESS_INST_ID ACTIVITY_ID 1 ACTIVITY_NAME 1 ACTIVITY_ID 2 ACTIVITY_NAME 2
100 4 Act 2 2 Act 2

Any help or suggestions would be greatly appreciated

Kris

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-09 : 11:23:35

declare @t table (ACTIVITY_INST_ID int, ACTIVITY_ID int ,PROCESS_INST_ID int)
insert @t
select 100, 3, 100 union all
select 101, 1, 100 union all
select 102, 2, 100 union all
select 103, 4, 100

declare @r table (ACTIVITY_ID int ,ACTIVITY_NAME varchar(20))
insert @r
select 1, 'Act 1' union all
select 2, 'Act 2' union all
select 3, 'Act 3' union all
select 4, 'Act 4'

select PROCESS_INST_ID
, max(case when rn = 1 then ACTIVITY_ID else null end)
, max(case when rn = 1 then ACTIVITY_NAME else null end)
, max(case when rn = 2 then ACTIVITY_ID else null end)
, max(case when rn = 2 then ACTIVITY_NAME else null end)
from
(
select t.rn,t.PROCESS_INST_ID,r.ACTIVITY_ID,r.ACTIVITY_NAME
from (
select row_number() over (partition by PROCESS_INST_ID order by ACTIVITY_INST_ID desc) as rn
, ACTIVITY_INST_ID
,ACTIVITY_ID
,PROCESS_INST_ID from @t ) t
inner join @r r on t.ACTIVITY_ID = r.ACTIVITY_ID
and t.rn < 3
) t1
group by PROCESS_INST_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources