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

what version of SQL Server you running? SQL 2012 introduced FETCH/OFFSET which is almost identical in function to the LIMIT syntax from MySQL — Xedni
– Xedni, Commented Jul 12, 2017 at 3:06

Tim Biegeleisen · Accepted Answer · 2017-07-12 02:00:54Z

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

edited Jul 12, 2017 at 2:00

answered Jul 12, 2017 at 1:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

teach me Over a year ago

row_number() is working fine and I have changed a little bit in OVER instead of ICDCode I used column name directly id.value @Tim Biegeleisen

Collectives™ on Stack Overflow

how to change query from MYSQL to SQL SERVER for LIMIT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related