Solved

SQL query required from single table

Posted on 2007-03-26
7
264 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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 
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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

631 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