CODEWITHSUNDEEP
sqlsql-serverselectjoinlimit
mondayguy
mondayguy

Reputation: 991

SQL limit with joins

I have problems with getting only first row of next query:

Select mar.Title MarketTitle, ololo.NUMBER, ololo.Title from Markets mar
JOIN( 
    Select emp.Employee_ID, emp.Market_id, ppp.Title, ppp.NUMBER from Employees emp
    JOIN(
    select pur.Employee_id Empl,Pro.Title Title, COUNT(pur.Product_id) NUMBER from Purchase pur
    LEFT JOIN  
        Payments pay ON pay.Payment_ID = pur.Payment_ID AND pay.Date > DATEADD(month, -1, GETDATE())
    RIGHT JOIN Products pro
    ON pur.Product_id = pro.Product_ID
    GROUP BY Pro.Title, pur.Employee_id) ppp ON ppp.Empl = emp.Employee_id) ololo
ON mar.Market_ID = ololo.Market_id
ORDER BY NUMBER DESC

I tried a lot of combination of inserting "limit 1", but all of them get errors. Could anyone help?

Upvotes: 3

Views: 64

Answers (2)

JBrooks
JBrooks

Reputation: 10013

It is not clear from your SQL what you are trying to retrieve. As it is, you are finding the highest quantity of a single product sold by a single employee in all of time. Then you are returning his market and the name of that product. Your pay.Date > dateadd(...) has no affect since it is in a LEFT JOIN. Since you are not returning any employee info, I don't think this is what you want...

If you want the product with the highest quantity in a given market(?) the SQL would be:

    select top 1 mar.Title Market, 
    pro.Title Product,
    count(1) NUMBER
    from Markets mar
    join Employees emp
    on mar.Market_Id = emp.Market_id
    join Purchase pur
    on emp.Employee_id = pur.Employee_id 
    join Products pro
    on pur.Product_id = pro.Product_ID
    where pur.Date > DATEADD(month, -1, GETDATE())
    group by mar.Title, pro.Title
    order by count(1) desc

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

LIMIT will not work with SQL Server.

Use TOP 1 instead:

SELECT TOP 1 mar.Title MarketTitle, ololo.NUMBER, ololo.Title from Markets mar
JOIN( 
    Select emp.Employee_ID, emp.Market_id, ppp.Title, ppp.NUMBER from Employees emp
    JOIN(
    select pur.Employee_id Empl,Pro.Title Title, COUNT(pur.Product_id) NUMBER from Purchase pur
    LEFT JOIN  
        Payments pay ON pay.Payment_ID = pur.Payment_ID AND pay.Date > DATEADD(month, -1, GETDATE())
    RIGHT JOIN Products pro
    ON pur.Product_id = pro.Product_ID
    GROUP BY Pro.Title, pur.Employee_id) ppp ON ppp.Empl = emp.Employee_id) ololo
ON mar.Market_ID = ololo.Market_id
ORDER BY NUMBER DESC

or OFFSET-FETCH with SQL Server 2012+:

SELECT  mar.Title MarketTitle, ololo.NUMBER, ololo.Title from Markets mar
JOIN( 
    Select emp.Employee_ID, emp.Market_id, ppp.Title, ppp.NUMBER from Employees emp
    JOIN(
    select pur.Employee_id Empl,Pro.Title Title, COUNT(pur.Product_id) NUMBER from Purchase pur
    LEFT JOIN  
        Payments pay ON pay.Payment_ID = pur.Payment_ID AND pay.Date > DATEADD(month, -1, GETDATE())
    RIGHT JOIN Products pro
    ON pur.Product_id = pro.Product_ID
    GROUP BY Pro.Title, pur.Employee_id) ppp ON ppp.Empl = emp.Employee_id) ololo
ON mar.Market_ID = ololo.Market_id
ORDER BY NUMBER DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY

Upvotes: 3

Related Questions