how to change query from MYSQL to SQL SERVER for LIMIT

Question

how to change this MYSQL query to sql server

SELECT
    id.value AS ICDCode,
    items.itemName AS ProblemListDescription,
    COUNT(*) as UsageCount
FROM problemlist pl
INNER JOIN items
    ON pl.asmtId = items.itemId
LEFT OUTER JOIN itemdetail id
    ON (items.itemId=id.itemId AND id.propId=13 )
WHERE (pl.SNOMED='' OR pl.SNOMED IS NULL) AND pl.deleteflag=0
group by id.value, items.itemName
ORDER BY UsageCount DESC, ICDCode ASC
LIMIT 0,10 ;

I have tried this for sql server but its throwing error

select * from
(
    SELECT
        id.value AS ICDCode,
        items.itemName AS ProblemListDescription,
        COUNT(*)as UsageCount ,
        row_number() over (ORDER BY UsageCount DESC, ICDCode ASC ) as rownum
    FROM problemlist pl
    INNER JOIN items
        ON pl.asmtId=items.itemId
    LEFT OUTER JOIN itemdetail id
        ON (items.itemId=id.itemId AND id.propId=13 )
    WHERE (pl.SNOMED='' OR pl.SNOMED IS NULL) AND pl.deleteflag=0 
    group by id.value, items.itemName
) sno
WHERE rownum BETWEEN 0 AND 10 ;

error message is

• column usagecount is invalid

• column icdcode is invalid

what is the mistake or i have to do it in another way ? guide me

Answer 1

I would probably just use TOP here:

SELECT TOP 10
    id.value AS ICDCode,
    items.itemName AS ProblemListDescription,
    COUNT(*) as UsageCount
FROM problemlist pl
INNER JOIN items
    ON pl.asmtId = items.itemId
LEFT OUTER JOIN itemdetail id
    ON (items.itemId=id.itemId AND id.propId=13 )
WHERE (pl.SNOMED='' OR pl.SNOMED IS NULL) AND pl.deleteflag=0
GROUP BY
    id.value,
    items.itemName
ORDER BY
    UsageCount DESC, ICDCode

By the way, the error in your query is that you were referring to an alias in the ROW_NUMBER function, but the alias is not yet available at that point in the query. You could use the following instead:

ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, ICDCode) AS rownum