0

I need to be able to show the items that has the top 10 highest values (quantity*price). In MySQL you can use LIMIT, but that's not possible in SQL Server. How can I achieve my goal?

Thanks in advance

SELECT ItemID, Itemname, Quantity, Price,
CONVERT(Decimal(8,0),ROUND((Quantity*price),2)) AS Total
FROM Item
holyredbeard
  • 19,619
  • 32
  • 105
  • 171
  • possible duplicate of [Emulate MySQL LIMIT clause in Microsoft SQL Server 2000](http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000) – CanSpice Feb 14 '12 at 23:17
  • 3
    It's in yr title! SELECT TOP 10 ... ORDER BY q*p DESC. – Andrew Feb 14 '12 at 23:17

4 Answers4

7
SELECT TOP 10 ItemID, Itemname, Quantity, Price,
CONVERT(Decimal(8,0),ROUND((Quantity*price),2)) AS Total
FROM Item
ORDER BY Quantity * Price DESC

The ORDER BY Quantity * Price DESC will ensure that the highest values are returned first.

Ryan
  • 26,884
  • 9
  • 56
  • 83
  • Sometimes it's easier than you think. Thanks a lot! – holyredbeard Feb 14 '12 at 23:26
  • You can also just say `ORDER BY Total DESC` - since `ORDER BY` is processed last, it's the only place in a single query where you can refer to aliases in the `SELECT` list. Doesn't change the answer, just leads to slightly cleaner code. – Aaron Bertrand Feb 15 '12 at 05:42
4
SELECT TOP 10 TItemID, Itemname, Quantity, Price,
CONVERT(Decimal(8,0),ROUND((Quantity*price),2)) AS Total
FROM Item
ORDER BY Total DESC
Bruno Silva
  • 3,077
  • 18
  • 20
1

You would use the TOP clause.

Hauke Ingmar Schmidt
  • 11,559
  • 1
  • 42
  • 50
1
SELECT TOP 10 ItemID, ...

Maybe this will help you? Also look BOTTOM keyword.

apocalypse
  • 5,764
  • 9
  • 47
  • 95