SQL group by novice - help please 1

[orangeseatbelt]

Hi, I can create queries using the grid in Access, but I'm trying to get used to writing SQL and understand Group By queries. When a group by is very simple I can (just about) manage it, but the following problem has me beat - any help would be great.

In the following small table I can write a SQL statement to retrieve the minimum price for each custName group, BUT I also want to retrieve the ID for that minimum price - and that's when I get incorrect results.

When I write:

SELECT Min(Sales.Price), Sales.CustName
FROM Sales
GROUP BY Sales.CustName;

that works just fine. So then I think I can just modify the above statement by putting min(Sales.ID) before/after the min(Sales.Price) and although that runs - it doesn't give me the ID from the min(price) row.

thanks for reading.

ID Date Price Quantity custName
1 01-Jan-11 160 2 smith
2 02-Jan-11 190 2 johnson
3 03-Jan-11 500 5 baldwin
4 04-Jan-11 420 2 smith
5 05-Jan-11 1000 4 wood
6 06-Jan-11 820 4 smith
7 07-Jan-11 22 2 baldwin

 

[r937]

SELECT t.CustName
     , t.Price
     , t.ID
     , t.Date
     , t.Quantity
  FROM ( SELECT CustName
              , Min(Price) AS min_price
           FROM Sales
         GROUP 
             BY CustName ) AS m
INNER
  JOIN Sales AS t
    ON (
       t.CustName = m.CustName
   AND t.Price = m.min_price
       )

the extra parentheses in the ON clause aren't usually required, but i think Access needs them



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon