SQL LIMIT 1 DESC order

Question

I am trying to show only the highest selling product per work center, however, it keeps telling me that LIMIT 1 is the incorrect syntax. I cant seem to find an answer on here that has helped so I decided to ask the question. Here's my query.

SELECT WorkCenter.WorkCenterCode, Product.Name, SUM(CustomerOrderLine.Quantity*CustomerOrderLine.ActualPrice) AS 'Total Sales'
FROM WorkCenter 
    INNER JOIN Product ON WorkCenter.WorkCenterCode = Product.WorkCenterCode 
    INNER JOIN CustomerOrderLine ON Product.ProductID = CustomerOrderLine.ProductID
    GROUP BY WorkCenter.WorkCenterCode, Product.Name
    ORDER BY 'Total Sales' DESC 
    LIMIT 1

What database are you using? Please add the appropriate tag to your question. — Gordon Linoff, Apr 24 '14 at 02:42
SQL Server Management studio and Visual Studio to create the reports — Danster09, Apr 24 '14 at 02:43

Gordon Linoff · Accepted Answer · 2014-04-24T03:08:50.070

3

Here is your query cleaned up a bit:

SELECT  wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
     Product p
     ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
     CustomerOrderLine col
     ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC
LIMIT 1

Note the important change from double quotes to single quotes. This is especially important for the order by clause so the clause actually does something, rather than sorting by a constant. The addition of table aliases makes the query easier to read.

If you are using Visual Studio, you should use top rather than limit:

SELECT TOP 1 wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
     Product p
     ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
     CustomerOrderLine col
     ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC;

EDIT:

For one row per work center, use this as a subquery with row_number():

SELECT WorkCenterCode, Name, "Total Sales"
FROM (SELECT wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales",
             row_number() over (partition by wc.WorkCenterCode order by SUM(col.Quantity*col.ActualPrice) desc) as seqnum
      FROM WorkCenter wc INNER JOIN
           Product p
           ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
           CustomerOrderLine col
           ON p.ProductID = col.ProductID
      GROUP BY wc.WorkCenterCode, p.Name
     ) t
WHERE seqnum = 1
ORDER BY "Total Sales" DESC;

edited Apr 24 '14 at 03:08

answered Apr 24 '14 at 02:44

Gordon Linoff

1,122,135
50
484
624

That worked, I kept switching back and forth between Visual Studio and SQL Server and it messed me up. That shows the top Work Center, I thought the way I had written it, that it would show the top selling for all work centers. – Danster09 Apr 24 '14 at 02:48
Im sure it is a simple fix, but how do I filter it to just one row per work center in order to show just the top highest selling product per work center. – Danster09 Apr 24 '14 at 02:58
Use `ROW_NUMBER()` - see below. – Michael Green Apr 24 '14 at 02:59
@Gordon, I am genuinely intrigued. I've just looked through the whole [TSQL spec](http://technet.microsoft.com/en-us/library/ms189499.aspx) for `SELECT` and can't find the `LIMIT` clause anywhere. What version of SQL Server are you running against? I want to learn about this, please. – Michael Green Apr 24 '14 at 03:07
1

@MichaelGreen . . . I don't understand your question. The point of my answer is that SQL Server uses `TOP` and not `LIMIT` (although the most recent version does now support `FETCH FIRST 1 ROWS ONLY`). – Gordon Linoff Apr 24 '14 at 03:10
@Gordon - so the first block of code in your answer is not a proposed solution, just a re-statement of his question, refactored for clarity. Fair enough; I understand now. Thanks for replying. – Michael Green Apr 24 '14 at 03:16
@MichaelGreen . . . Yes. The query actually had another fatal problem in it. – Gordon Linoff Apr 24 '14 at 03:17

score 0 · Answer 2 · answered Apr 24 '14 at 02:44

For SQL Server use SELECT TOP 1

SELECT TOP 1 WorkCenter.WorkCenterCode, Product.Name, SUM(CustomerOrderLine.Quantity*CustomerOrderLine.ActualPrice) AS [Total Sales]
FROM            WorkCenter INNER JOIN
                     Product ON WorkCenter.WorkCenterCode = Product.WorkCenterCode INNER     JOIN
                     CustomerOrderLine ON Product.ProductID = CustomerOrderLine.ProductID
GROUP BY WorkCenter.WorkCenterCode, Product.Name
ORDER BY [Total Sales] DESC

Why? What did you change? Code-only answers are not as useful without an explanation. — random_user_name, Apr 24 '14 at 02:45

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

Here's another SO question on the topic which uses ROW_NUMBER(). TOP 1 will only return one row in total whereas ROW_NUMER() can return one row per group.

edited May 23 '17 at 11:58

Community

1
1

answered Apr 24 '14 at 02:57

Michael Green

1,299
1
16
23

SQL LIMIT 1 DESC order

3 Answers3