Grouping & Aggregate Functions?

[androidx]

I have a table that contains 3 fields: Carrier, BuyRate, & Destination. A carrier provides a rate to a select destination. What I'm trying to do is create a recordset that contains the cheapest rate for each destination as well as the carrier who can provide that rate.

SELECT MIN(BuyRate), Destination FROM FullList
GROUP BY Destination;

This is able to pull up the destination and lowest rate but I can't figure out how to link the carrier name that provides that rate to the list. Whenever I try I get multiple rates for the same destination or this error:

You tried to execute a query that does not include the specified expression 'Carrier' as part of an aggregate function.

If anyone could help me out I'd greatly appreciate it!!

Thanks.

 

[dodge20]

I think you will just want to put the carrier in the select and the group by

SELECT MIN(BuyRate), Destination, Carrier
FROM FullList
GROUP BY Destination, Carrier;


Dodge20

 

[androidx]

That's what I would have thought to. Although when I try that it gives me multiple instances of each destination. In other words it just gives me the listing of the full table.

 

[dodge20]

I see what you are asking now. You want to list the the carrier name along with the destination. The way I had it before, it would show the same destination again if it had the same carrier.

Try this

SELECT BuyRate, Destination, Carrier
FROM FullList
WHERE BuyRate IN
(select MIN(BuyRate)
From FullList)




Dodge20

 

[swampBoogie]

You need to correlate the subquery to get the cheapest for each destination

SELECT BuyRate, Destination, Carrier
FROM FullList F
WHERE BuyRate IN
(select MIN(BuyRate)
From FullList
where destination = f.destination)

 

[dodge20]

Thank you swamp boogie. I had a brain lapse. There is nothing worse than a monday except for a tuesday following a 3 day weekend.

Dodge20