sql - Struggling to find the right WHERE clause -
i'm struggling sql query , need help. honest, i'm starting wonder if want achieve can done way did far maybe collective brains can come better solution mine , prove me took way @ beginning (or totally wrong , should start scratch).
the dataset
a row has 4 important fields: itemid, item, priority , group. fields contain valuable piece of information, 1 displayed in end.
as i'm using sql server 2008, don't have access lag , lead function needed simulate them (or @ least, did because thought useful me i'm not sure anymore). obtain result, used code this article sqlscope provide lag , lead equivalent restrict set of row have same itemid. adds 7 new functional columns dataset: rn, rndiv2, rnplus1div2, previouspriority, nextpriority, previousgroup , nextgroup.
itemid | item | priority | group | rn | rndiv2 | rnplus1div2 | previouspriority | nextpriority | previousgroup | nextgroup -------- | ------- | -------- | ------- | ----- | ------ | ----------- | ---------------- | ------------ | ------------- | --------- 16777397 | item 1 | 5 | group 1 | 1 | 0 | 1 | null | null | null | null 16777403 | item 2 | 5 | group 2 | 1 | 0 | 1 | null | 5 | null | group 2 16777403 | item 2 | 10 | group 2 | 2 | 1 | 1 | 5 | null | group 2 | null 16777429 | item 3 | 1000 | group 3 | 1 | 0 | 1 | null | null | null | null 16777430 | item 4 | 5 | group 1 | 1 | 0 | 1 | null | null | null | null 16777454 | item 5 | 5 | group 4 | 1 | 0 | 1 | null | null | null | null 16777455 | item 6 | 5 | group 5 | 1 | 0 | 1 | null | null | null | null 16777459 | item 6 | 5 | group 6 | 1 | 0 | 1 | null | null | null | null 16777468 | item 8 | 5 | group 7 | 1 | 0 | 1 | null | null | null | null 16777479 | item 9 | 5 | group 4 | 1 | 0 | 1 | null | null | null | null 16777481 | item 10 | 5 | group 4 | 1 | 0 | 1 | null | null | null | null 16777496 | item 11 | 5 | group 6 | 1 | 0 | 1 | null | null | null | null 16777514 | item 12 | 5 | group 4 | 1 | 0 | 1 | null | null | null | null 16777518 | item 13 | 5 | group 8 | 1 | 0 | 1 | null | 10 | null | group 8 16777518 | item 13 | 10 | group 8 | 2 | 1 | 1 | 5 | 100 | group 8 | group 1 16777518 | item 13 | 100 | group 1 | 3 | 1 | 2 | 10 | null | group 8 | null 16777520 | item 14 | 5 | group 9 | 1 | 0 | 1 | null | null | null | null the problem
the problem in sql query where clause. filter rows based on group column. there subtlety. whatever number of group item member of, want appear in one , one group based on these criteria :
- if
itemappears in samegroupmore 1 time, line lowestpriorityshould returned. ifitemappears more 1 time in samegroupsamepriority, first occurrence should kept example:item 2, linepriorityvalue of 5 should returned; - if
itemappears ingrouppresent ingrouplowestpriority, shouldn't displayed. example:group 1selected filter.item 1should displayeditem 13shouldn't because present ingroup 8lowerpriority(item 13appear ingroup 8).
note sample. real dataset has more 3000 rows , other cases possible haven't listed in sample.
unsuccessful attempts
like said, there 1 constant in where clause , group filtering.
- because of criterion #2, can't start clause :
where group = 'group 1', need have bit more complex. - i have tried following clause without success :
where group = 'group 1' , (group = nextgroup , priority < nextpriority). works in case ofitemin no more 2 groups.item 13, return first 2 rows. , if addand not (correctedpriority >= previouspriority)whereclause, no results @ all. - last attempt far :
(sitename <> previoussitename , correctedpriority >= previouspriority). problem never return linern = 1becauseprevioussitenameequalnull. adding check onnulldoesn't work either. must have bee tired when trying particular clause because it's complete garbage.
i continue try , find where clause have feeling whole approach wrong. don't see how solve problem when there more 2 entries same item. worth noting query used in ssrs report maybe use custom code parse dataset , filter rows (working tables might solving issue of items more 2 entries). if there's sql genius around here working solution, great.
ps : if knows how fix table , can explain me, cookies him. :d
edit :
this modified query i'm using @ moment. consider using @yellowbedwetter's latest query has seems more robust.
select * (select itemid, item, priority, group_, min(priority) on ( partition item ) interitem_minpriority (select itemid, item, priority, group_, row_number() on ( partition item order priority asc ) intergrp_rank test_table ) tmp intergrp_rank = 1 -- exclude records same item/group, higher priority. ) tmp2 priority = interitem_minpriority; -- exclude aren't lowest priority across groups.
if understand question correctly should work
select * (select itemid, item, priority, group_, min(priority) on ( partition item ) interitem_minpriority (select itemid, item, priority, group_, row_number() on ( partition item, group_ order priority asc ) intergrp_rank test_table ) tmp intergrp_rank = 1 -- exclude records same item/group, higher priority. ) tmp2 priority = interitem_minpriority; -- exclude aren't lowest priority across groups. i don't know if version of sql server supports min() over()..., if not should able work around enough.
edit: handle tie breaks.
with test_table (itemid, item, priority, group_) ( select '16777397','item 1','5','group 1' union select '16777403','item 2','5','group 2' union select '16777403','item 2','10','group 2' union select '16777429','item 3','1000','group 3' union select '16777430','item 4','5','group 1' union select '16777454','item 5','5','group 4' union select '16777455','item 6','5','group 5' union select '16777459','item 6','5','group 6' union select '16777468','item 8','5','group 7' union select '16777479','item 9','5','group 4' union select '16777481','item 10','5','group 4' union select '16777496','item 11','5','group 6' union select '16777514','item 12','5','group 4' union select '16777518','item 13','5','group 8' union select '16777518','item 13','10','group 8' union select '16777518','item 13','100','group 1' union select '16777520','item 14','5','group 9' ) select itemid, item, priority, group_ (select itemid, item, priority, group_, row_number() on ( partition item order group_ asc -- or want break tie ) grp_minpriority_tiebreak (select itemid, item, priority, group_, min(priority) on ( partition item ) interitem_minpriority (select itemid, item, priority, group_, row_number() on ( partition item, group_ order priority asc ) intergrp_rank test_table ) tmp intergrp_rank = 1 -- exclude records same item/group, higher priority. ) tmp2 priority = interitem_minpriority -- exclude aren't lowest priority across groups. ) tmp2 grp_minpriority_tiebreak = 1; wiki
Comments
Post a Comment