Query minimum value of distinct records

[learner45]

Hi,

I have a view:
CustomerID OrderID ItemCost City
10 13 4500 A
30 17 3700 C
20 25 3457 B
50 19 7000 E
10 21 1900 A
40 32 2345 D
30 67 3457 C
20 89 4568 B

The field 'OrderID' is unique;

Is there a way to list the minimum 'ItemCost' by each 'CustomerId' - and the related 'OrderID' and 'City' ?

Thanks to all the helpful folks!

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT CustomerId, 
                   MIN(ItemCost) AS ItemCost 
            FROM YourTable
            GROUP BY CustomerId) Tbl1
      ON YourTable.CustomerId = Tbl1.CustomerId
         YourTable.ItemCost   = Tbl1.ItemCost

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[learner45]

Sorry! It's not working

 

[bborissov]

OK, what you want to get from the data you post?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

The field 'OrderID' is unique;

so how come there's two rows with order 3457?

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