-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
  • 21,987
  • 13
  • 41
  • 69
Đồ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
  • Sorry. I fixed that! Thank you. – Đồng Phúc Thiên Quốc May 29 '22 at 08:08
  • 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 NewQuerry
ORDER BY Sales DESC;
Tim Biegeleisen
  • 451,927
  • 24
  • 239
  • 318