Solved

SQL query required from single table

Posted on 2007-03-26
7
218 Views
Last Modified: 2010-03-20
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.
0
Comment
Question by:LeeGolding
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 18796088
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
 

Author Comment

by:LeeGolding
ID: 18796133
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
 
LVL 9

Expert Comment

by:TheSloath
ID: 18796141
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 13

Expert Comment

by:adraughn
ID: 18796199
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
 

Author Comment

by:LeeGolding
ID: 18796890
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
 
LVL 9

Accepted Solution

by:
TheSloath earned 250 total points
ID: 18797099
Turkey has several Airports. If you want unique countries:

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

Author Comment

by:LeeGolding
ID: 18797136
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access DLookup 18 29
oracle query help 18 77
Oracle Subquery bad Join 11 42
How to convert sql code to access query 8 54
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now