SQL query required from single table

Hi friends,

I am trying to get a list of 18 holiday destinations ordered by price cheapest first. However, all destinations must be unique. Here is a sample of the table data:

Turkey      253      EMA      AYT
Turkey      259      EMA      AYT
Turkey      261      EMA      AYT
Turkey      264      EMA      AYT
Portugal      260      BHX      FAO
Portugal      261      BHX      FAO
Malta      285      LTN      MLA
Malta      286      LTN      MLA
Turkey      331      BHX      BJV
Turkey      332      BHX      BJV
Croatia      629      BHX      DBV
Tunisia      328      BHX      MIR
Greece      498      BHX      EFL
Greece      529      BHX      EFL
Greece      611      BHX      EFL
Greece      636      BHX      EFL
Greece      310      BHX      EFL
Greece      317      BHX      EFL

This is the best so far, but the destinations aren't unique:

SELECT DestCountry, AdultPricePP, DeptAirportCode, DestAirportCode
FROM hd
GROUP BY AdultPricePP, DeptAirportCode, DestAirportCode, DestCountry

Lee.
LeeGoldingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adraughnCommented:
try this:
SELECT DISTINCT DestCountry, AdultPricePP, DeptAirportCode, DestAirportCode
FROM hd
ORDER BY AdultPricePP DESC

as long as you have AdultPricePP in the select, it will return a record for each price. how exactly do you want the data to look?
0
LeeGoldingAuthor Commented:
Not what I want.

SELECT DISTINCT DestCountry, AdultPricePP, DeptAirportCode, DestAirportCode
FROM hd
ORDER BY AdultPricePP ASC

Produces:

Malta      112      LTN      MLA
Malta      117      LTN      MLA
Malta      124      LTN      MLA
Malta      125      LTN      MLA
Malta      131      LTN      MLA
Malta      141      LTN      MLA
Malta      143      LTN      MLA
Malta      146      LTN      MLA
Spain      151      LGW      PMI
Malta      153      LTN      MLA
Portugal      153      LGW      FAO
Malta      154      LTN      MLA
Malta      155      LTN      MLA
Italy      156      EMA      AHO
Malta      156      LTN      MLA
Malta      157      LTN      MLA
Malta      158      LTN      MLA
Malta      159      LTN      MLA


I need:

Italy 129
Malta 130
Portugal 150

Even though there may be several prices that are lower than the Malta one at 130.

Lee.
0
TheSloathCommented:
If you just want the cheapest price for a Destination:

SELECT DestCountry, MIN([AdultPricePP]) As MinAdultPricePP, DeptAirportCode, DestAirportCode
FROM hd
GROUP BY DeptAirportCode, DestAirportCode, DestCountry
ORDER BY MinAdultPricePP
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

adraughnCommented:
How about something like this?

SELECT DestCountry, MIN(AdultPricePP) AS LowestPrice, DeptAirportCode, DestAirportCode
FROM hd
GROUP BY DestCountry, DeptAirportCode, DestAirportCode
ORDER BY MIN(AdultPricePP ASC


0
LeeGoldingAuthor Commented:
No. That give mes me. Still no unique countries. Strange.

Malta      112      LTN      MLA
Spain      151      LGW      PMI
Portugal      153      LGW      FAO
Italy      156      EMA      AHO
Spain      160      BHX      PMI
Greece      161      BHX      RHO
Spain      162      EMA      GRO
Spain      166      EMA      PMI
Portugal      169      BHX      FAO
Turkey      173      EMA      DLM
Tunisia      179      BHX      MIR
Turkey      180      BHX      DLM
Turkey      180      LGW      BJV
Turkey      180      BHX      BJV
Spain      184      LGW      TFS
Greece      190      LGW      HER
Spain      192      EMA      ALC
Turkey      192      BHX      AYT


Lee.
0
TheSloathCommented:
Turkey has several Airports. If you want unique countries:

SELECT DestCountry, MIN([AdultPricePP]) As MinAdultPricePP
FROM hd
GROUP BY DestCountry
ORDER BY MinAdultPricePP
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeGoldingAuthor Commented:
I think that is correct.

So its just impossible to get the SQL to ignore that there are multiple airports if we are selecting them in the statement. I understand.

Lee.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.