Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query required from single table

Posted on 2007-03-26
7
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1000 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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

705 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