CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
TyForHelpDude
TyForHelpDude

Reputation: 5001

row number broken when its used with CTE

This is my query the SQL inside CTE works fine and all I want to get first 20 rows for each city name:

WITH taniCte
AS (
    SELECT sl.Adi AS [CityName],
        mt.ICD10Kodu AS [Tanı],
        count(mt.ICD10Kodu) AS sayi,
        RowNum = ROW_NUMBER() OVER (
            ORDER BY sl.Adi
            )
    FROM Muayene.Muayene mm WITH (NOLOCK)
    INNER JOIN Muayene.Tani mt WITH (NOLOCK) ON mm.ID = mt.MuayeneId
    INNER JOIN Ortak.Kurum ok WITH (NOLOCK) ON mm.CreatedKurumKodu = ok.KurumKodu
    INNER JOIN Skrs.Il sl WITH (NOLOCK) ON ok.IlKodu = sl.Kodu
    GROUP BY sl.Adi,
        mt.ICD10Kodu
    ) --order by [CityName], sayi desc // commentewhen its moved inside cte
SELECT [CityName],
    [Tanı],
    sayi,
    RowNum
FROM taniCte
WHERE RowNum <= 20
ORDER BY [CityName],
    sayi DESC

Eventhough main query works fine the query above returns wrong rows..

I couldn't find any solution on internet

Upvotes: 1

Views: 188

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

If you need 20 rows per city then your row_number OVER clause in cte should be 

with taniCte as
(
  select sl.Adi as [CityName]
       , mt.ICD10Kodu as [Tanı]
       , count(mt.ICD10Kodu) as sayi
       , RowNum = ROW_NUMBER()OVER(partition By sl.Adi order by count(mt.ICD10Kodu)) -- this change in over clause
  from Muayene.Muayene mm with(nolock)
       join Muayene.Tani mt with(nolock) on mm.ID = mt.MuayeneId
       join Ortak.Kurum ok with(nolock) on mm.CreatedKurumKodu = ok.KurumKodu
       join Skrs.Il sl with(nolock) on ok.IlKodu = sl.Kodu
   group by sl.Adi, mt.ICD10Kodu 
   --order by [CityName], sayi desc // commentewhen its moved inside cte
)
select [CityName], [Tanı],sayi, RowNum 
from taniCte 
where RowNum <= 20 
order by [CityName], sayi desc

Upvotes: 2

Related Questions