hellzone hellzone - 10 days ago 6
SQL Question

How to get first n distinct column value from left join?

What I want to get is "get first 5 distinct customer's order id values". When I limit below query to 5 it only returns first 4 customer's order id values as expected.(Around the Horn has two different order id) Is there a way to get first 5 distinct customer's order id values?

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;


CustomerName OrderID
Alfreds Futterkiste 10001
Ana Trujillo Emparedados y helados 10308
Antonio Moreno Taquería 10365
Around the Horn 10355
Around the Horn 10383
B's Beverages 10289
Antonio baa 10444
.... ....


EDIT: What I want to get is first 5 distinct customer;

Alfreds Futterkiste 10001 (first customer)
Ana Trujillo Emparedados y helados 10308 (second customer)
Antonio Moreno Taquería 10365 (third customer)
Around the Horn 10355 (forth customer)
Around the Horn 10383 (forth customer)
B's Beverages 10289 (fifth customer)


Here is the tables I am working on;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_left

Answer

Try This.

            SELECT DISTINCT Orders.OrderID, CC.CustomerName

            FROM Customers CC
            LEFT JOIN Orders
            ON CC.CustomerID=Orders.CustomerID
            inner join  

            (
            SELECT DISTINCT Customers.CustomerName
            FROM Customers
            LEFT JOIN Orders
            ON Customers.CustomerID=Orders.CustomerID
            ORDER BY Customers.CustomerName
            LIMIT 5
            ) C 
            ON C.CustomerName=CC.CustomerName

            ORDER BY CC.CustomerName