Jul
27
Written by:
Javier Callico
7/27/2007
Today I was asked to look into an interesting task that was keeping busy one of my colleagues for a while. It's such a common task that I'm wondering how come I was never presented with something similar before.
Let's use an example to better illustrate the task on hand. Imagine you have a notification system that runs daily and creates a list of different events for what individual users need to be notified.
This table represents the notification list resulted from the daily run:
UserId EventId
-------------------
1 1
1 2
1 3
2 2
3 2
3 4
3 5
4 2
Now a new requirement comes up: All events will be assigned a priority and the users can only received 2 notifications a day for the events with the highest priority.
This table represents our Events and their Priority (the bigger the number the highest is the priority):
EventId Priority
--------------------
1 8
2 9
3 10
4 6
5 7
In order to satisfy the new requirement these will be the new notification to be sent:
UserId EventId
-------------------
1 3
1 2
2 2
3 2
3 5
4 2
Note that now user 1 will not receive the event 1 and user 3 will not receive event 4.
So, how you model this problem and select the desired data using standard SQL without using cursors or dynamic SQL? See the script below:
create table #notification
(
userid int,
eventid int
)
create table #event
(
eventid int,
priority int
)
insert into #notification values (1,1)
insert into #notification values (1,2)
insert into #notification values (1,3)
insert into #notification values (2,2)
insert into #notification values (3,2)
insert into #notification values (3,4)
insert into #notification values (3,5)
insert into #notification values (4,2)
insert into #event values (1,8)
insert into #event values (2,9)
insert into #event values (3,10)
insert into #event values (4,6)
insert into #event values (5,7)
select n.userid, e.eventid, e.priority
into #temp
from #notification n
join #event e on e.eventid = n.eventid
select userid, eventid
from #temp t
where (
select count(distinct priority)
from #temp
where userid = t.userid
and priority >= t.priority
) <= 2
order by userid, priority desc, eventid
drop table #temp
drop table #notification
drop table #event
It's very easy to modify this script to use a variable number of rows and not only 2.
Tags: