CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
devammmm
devammmm

Reputation: 37

Using ROW_NUMBER in CTE

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

Answers (4)

FutbolFan
FutbolFan

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

Vamsi Prabhala
Vamsi Prabhala

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

Shannon Severance
Shannon Severance

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

Gordon Linoff
Gordon Linoff

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

Related Questions