How to use limit the results returned from SQL Server using ROW_NUMBER()

Question

I have database as below

CREATE DATABASE Test2;

CREATE TABLE table1
(
    name nvarchar(50),
    year int,
    total1 int,
    total2 int
);

INSERT INTO table1 (name, year, total1,total2)
VALUES ('a', 2020, 25,3);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('b', 2018, 33,4);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('c', 2020, 10,3);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('b', 2018, 7,2);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('a', 2020, 20,6);

I want to limit the results returned from SQL Server (take 2nd row and 3rd row) with this code

select 
    *
from
    (select 
         year, name, 
         sum(total1) as "sum_Total1", 
         sum(total2) as "sum_Total2", 
         round((cast(isnull(sum(total2), 0) as float)) / (cast(sum(total1) as float)), 3) as "sum_Total2/sum_Total1", 
         row_number() over (order by round((cast(isnull(sum(total2), 0) as float)) / (cast(sum(total1) as float)), 3) asc) as no
     from 
         Table_1
     group by 
         name, year
     order by 
         round((cast(isnull(sum(total2), 0) as float)) * 100 / (cast(sum(total1) as float)), 3) asc) a
where 
    a.no > 1 and a.no < 3

SQL Server return an error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Salah Akbari · Accepted Answer · 2020-04-16 04:07:17Z

1

Actually since you have already used the Order By in this line ROW_NUMBER() over(ORDER BY ROUND you don't need to use it again in your inner query after grouping. So all that you need to do is removing the unnecessary order by after your group by keyword.

Also FYI, I can see that you've queried from Table_1 while your table name is table1, so you need to fix it as well.

edited Apr 16, 2020 at 4:07

answered Apr 15, 2020 at 16:49

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

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

Comments

sacse · Accepted Answer · 2020-04-15 16:55:41Z

there are two issues:

You are not using the same table which you created.
to get the 2nd and 3rd row, you'll need to change the condition a.no < 3 to a.no <= 3

there is no use of order by clause as we have no.

Finally:

SELECT *
FROM
(
    SELECT year, 
           name, 
           SUM(total1) AS "sum_Total1", 
           SUM(total2) AS "sum_Total2", 
           ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) / (CAST(SUM(total1) AS FLOAT)), 3) AS "sum_Total2/sum_Total1", 
           ROW_NUMBER() OVER(
           ORDER BY ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) / (CAST(SUM(total1) AS FLOAT)), 3) ASC) AS no
    FROM Table1
    GROUP BY name, 
             year
    --ORDER BY ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) * 100 / (CAST(SUM(total1) AS FLOAT)), 3) ASC
) a
WHERE a.no > 1
      AND a.no <= 3;

Cetin Basoz · Accepted Answer · 2020-04-15 16:59:52Z

0

Just move the order by outside your subquery:

select * 
from
(
select year ,name,
sum(total1) as "sum_Total1", 
SUM(total2) as "sum_Total2", 
ROUND((CAST(ISNULL(sum(total2),0) as float))/ 
      (CAST(sum(total1) as float)),3) as "sum_Total2/sum_Total1", 
ROW_NUMBER() over (ORDER BY 
ROUND((CAST(ISNULL(sum(total2),0) as float))/ (CAST(sum(total1) as float)),3) ASC )  as no
from Table1
group by name, year
) a
where a.no > 1 and a.no < 4
order by no;

answered Apr 15, 2020 at 16:59

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

How to use limit the results returned from SQL Server using ROW_NUMBER()

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related