-1

I have a table SALE with 2 columns ID and Sales.

I have code like this:

SELECT TOP 100 PERCENT ID, Sales
FROM SALE
WHERE ProductID = 'IDN001'
ORDER BY Sales DESC;

And the result is here:

1st table

But if I put all the code above inside the SELECT * FROM, it shows me the original TABLE (before ordering):

SELECT *
FROM
    (SELECT TOP 100 PERCENT ID, Sales
     FROM SALE
     WHERE ProductID = 'IDN001'
     ORDER BY Sales DESC) AS NewQuery;

The new result is here:

2nd table

How can I fix this?

Thank you.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Đồng Phúc Thiên Quốc
  • 9
  • 2
  • 3
    Please fix your use of ALL CAPS in your title, people think you are SHOUTING at them. – Solar Mike May 29 '22 at 08:01
  • 6
    The only place an ORDER BY guarantees order of displayed results is on the outer query. TOP 100 PERCENT ... ORDER BY is [completely ignored](https://stackoverflow.com/a/1622964/73226) by the optimiser in derived tables, views etc. – Martin Smith May 29 '22 at 08:02
  • 1
    You need to apply the required ordering to the outer-most query; With no ordering criteria, ordering is not guaranteed exactly like it isn't when selecting from any other table. – Stu May 29 '22 at 09:05

1 Answers1

0

The ordering of a subquery does not "stick" in a SQL Server query. You need to add an explicit ORDER BY clause to the outer query to get the sorting behavior you want:

SELECT *
FROM
(
    SELECT TOP 100 PERCENT ID, Sales
    FROM SALE
    WHERE ProductID = 'IDN001'
    ORDER BY Sales DESC
) AS NewQuery
ORDER BY Sales DESC;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360