Select highest priority - 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

Select highest priority

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2009-09-24 : 05:37:33

Each individual can have same information from different sources.
The sources has different priority.
How to select the row with highest priority ?

create table #ind_events
(
individual int,
indeventtype nvarchar (50),
indvalue int,
indeventsource nvarchar (50)
)
insert into #ind_events
values
(1,'Birth',101,'A'),
(2,'Birth',102,'B'),
(2,'Birth',103,'C'),
(2,'Marriage',222,'A'),
(2,'Marriage',223,'B')

SELECT * FROM #ind_events

create table #sourcePriority
(
eventtype nvarchar (50),
eventsource nvarchar (50),
priority int
)
insert into #sourcePriority
values
('Birth','A',1),
('Birth','B',2),
('Birth','C',3),
('Marriage','A',2),
('Marriage','B',1)

SELECT * FROM #sourcePriority

/**

Birth sourcePriority: A,B,C
Marriage sourcePriority: B,C

individual indeventtype indvalue indeventsource
1 Birth 101 A
2 Birth 102 B
2 Birth 103 C
2 Marriage 222 A
2 Marriage 223 B

**/
create table #result
(
individual int,
indeventtype nvarchar (50),
indvalue int,
indeventsource nvarchar (50)
)

/**
RESULT should be:
individual indeventtype indvalue indeventsource

1 Birth 101 A
2 Birth 102 B
2 Marriage 223 B

**/

DROP TABLE #ind_events
DROP TABLE #sourcePriority
DROP TABLE #result

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-24 : 07:15:50

[code]
select i.individual, i.indeventtype, i.indvalue, i.indeventsource
from
(
select i.individual, i.indeventtype, i.indvalue, i.indeventsource,
row_no = row_number() over (partition by i.individual, i.indeventtype order by p.priority)
from #ind_events i
inner join #sourcePriority p on i.indeventtype = p.eventtype
and i.indeventsource = p.eventsource
) i
where i.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Clas
Starting Member

33 Posts

Posted - 2009-09-24 : 07:21:26

THANKS !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources