Solved

SQL query required from single table

Posted on 2007-03-26
7
227 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 52
SQL Server 2008 R2 - Sums/Grouping 7 51
Remove Time Stamp from a Date field 4 41
SQL Statement to Update Email Domain 2 21
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

22 Experts available now in Live!

Get 1:1 Help Now