this is a link to my data.
I have this query:
SELECT *
FROM tbl c
WHERE C.dep = (select top 1 dep
from tbl cc
where cc.yea = c.yea
and cc.mon = mon
group by mon, yea, dep, n
order by n desc)
OR C.dep =( select top 1 dep
from tbl cc
where cc.yea = c.yea
and cc.mon = mon
group by mon, yea, dep, n
order by n asc
)
ORDER BY yea, mon, n
that sould return for each (month,year) the best (lowest n) and the worst (highest n) dep. This query works for month 1,2,4,5,7 and not for months 3,6. The only difference is that in both 3 and 6 cases I got two del with same score (1). How can I return one of them, instead of not returning anything.
this is my output:
n yea mon dep
----------- ----------- ----------- ----------
1 2017 1 50
48 2017 1 36
58 2017 2 36
85 2017 3 36
1 2017 4 50
39 2017 4 36
1 2017 5 50
39 2017 5 36
19 2017 6 36
3 2017 7 50
17 2017 7 36
And this is how I expected:
n yea. mon dep
----------- ----------- ----------- ----------
1 2017 1 50
48 2017 1 36
58 2017 2 36
85 2017 3 36
1 2017 3 49 (or 67)
1 2017 4 50
39 2017 4 36
1 2017 5 50
39 2017 5 36
1 2017 6 50 (or 13)
19 2017 6 36
3 2017 7 50
17 2017 7 36
Use row_number():
select t.*
from (select t.*,
row_number() over (partition by yea, mon order by n asc) as seqnum_asc,
row_number() over (partition by yea, mon order by n desc) as seqnum_desc
from tbl t
) t
where 1 in (seqnum_asc, seqnum_desc);