Reputation: 37
I am trying for a CTE with a ROW_NUMBER function.
Query:
with Demo as
(
select *, ROW_NUMBER() over (partition by GID, MID order by MMID) as ROWNUMBER from Table1 where DateEntered > '2015-06-13 00:00:00.000'
)
select * from Demo
Here, the result I get is
GID MID ROWNUMBER
1 1 1
1 2 1
1 2 2
1 2 3
2 1 1
2 2 1
2 2 2
2 3 5
2 4 4
Now, I want to get all the rows where combination of GID,MID has max row number value. But a condition here is that for those rows, the combination of GID,MID should also have 1.
In simple terms, get me the rows with max row number value, if that combination of gid,mid has rownumber=1.
The output I desire is
GID MID ROWNUMBER
1 1 1
1 2 3
2 1 1
2 2 2
I hope i did not made it complex. Can anyone pls inform me on how to do this ?
Upvotes: 1
Views: 16546
Reputation: 13763
This should work for you:
with Demo as
(
select *,
ROW_NUMBER() over (partition by GID, MID order by MMID) as ROWNUMBER
from Table1
where DateEntered > '2015-06-13 00:00:00.000'
)
select GID,
MID,
MAX(ROWNUMBER) as MaxROWNUMBER
from Demo
GROUP BY GID,MID;
Upvotes: 1
Reputation: 49270
with Demo as
(
select *, ROW_NUMBER() over (partition by GID, MID order by MMID) as RN
from Table1 where DateEntered > '2015-06-13 00:00:00.000'
)
, x as
(select gid, mid
from demo
where RN = 1
)
select demo.gid, demo.mid, max(demo.rn) as rownumb
from demo left join x
on x.gid = demo.gid and x.mid = demo.mid
group by demo.gid, demo.mid;
You can use max
to select the highest rownumber per mid, gid combination.
Upvotes: 2
Reputation: 18430
The max of row_number()
is just count.
select GID, MID, COUNT(*) as ROWNUMBER
from Table1
where DateEntered > '2015-06-13 00:00:00.000'
group by GID, MID
Upvotes: 1
Reputation: 1271241
If you don't need the row number value, just use desc
instead of asc
and a filter:
with Demo as (
select t.*,
ROW_NUMBER() over (partition by GID, MID order by MID DESC) as seqnum
from Table1
where DateEntered > '2015-06-13'
)
select *
from Demo
where seqnum = 1;
If you do want a more meaningful ROWNUMBER
in the output, then use two calculations in the CTE.
Upvotes: 1