Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select Distinct on multiple columns

Posted on 2005-04-09
17
Medium Priority
?
255 Views
Last Modified: 2012-06-27
I have a sql 2000 DB with about 10 colunms
I only want to have firstname distinct but order by the lastname
and show the rest of the info for all the row *


My data is like this:

    dest      resort     price     id      type

   cancun      hilton     1000     111     star
   cancun      hilton     1000     222     tree
   cancun      hilton     1200     333     rock
   cancun      hilton     1300     444     bird
    paris        hilton     1900     555     bird
    paris        hilton     1000     666     bird
    paris        hilton     1200     777     rock
    paris        hilton     1300     888     tree

My query is like this:(It's wrong cause we have duplicates)

SELECT DISTINCT dest, resort, price, id, type etc........
FROM         mydb
WHERE price = the lowest price
ORDER BY lastname

I WANT my output to be like this:

paris hilton 1000 666 bird
0
Comment
Question by:korr88
  • 8
  • 3
  • 3
  • +1
17 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 13745320
You may want to tell why this row.
0
 

Author Comment

by:korr88
ID: 13745327
I need to find out the lowest price to Paris
0
 
LVL 17

Expert Comment

by:mokule
ID: 13745370
SELECT TOP * FROM YourTab
WHERE dest='paris'
ORDER BY price DESC
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:mokule
ID: 13745374

SELECT TOP 1 * FROM YourTab
WHERE dest='paris'
ORDER BY price DESC
0
 

Author Comment

by:korr88
ID: 13745410
ok thats easy,I knew that but, I guess I didnt make it clear enough sorry.

I actually need to get the lowest price for every destination. but spit out all the info int hat row as well.Thanks(now you see how my problem gets complex.)

0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13745412
SELECT dest, resort, price, id, type
FROM mydb
WHERE price in (Select Min(price) FROM mydb WHERE dest = 'paris')
and dest = 'paris'
Order by lastname

Anthony
0
 

Author Comment

by:korr88
ID: 13745416
And what if i want to get the lowest 10 prices from every destination?
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13745420
SELECT dest, resort, price, id, type
FROM mydb x
WHERE price in (Select Min(price) FROM mydb WHERE dest = x.dest)
Order by lastname

What a difference a minute makes :)
0
 

Author Comment

by:korr88
ID: 13745438
ok good job but now I have 4 that have a duplicate price.
0
 

Author Comment

by:korr88
ID: 13745442
I dont understand the x part of x.dest you just wrote anthony.???
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13745866
korr88, please try this one:

SELECT mydb3.*
FROM mydb mydb3 INNER JOIN (
SELECT mydb1.Dest, mydb1.Price, MIN(mydb1.ID) AS ID
FROM mydb mydb1 INNER JOIN (
SELECT Dest, MIN(Price) AS Price
FROM mydb
GROUP BY Dest) mydb2
ON mydb1.Dest  = mydb2.Dest AND
   mydb1.Price = mydb2.Price
GROUP BY mydb1.Dest, mydb1.Price) mydb4
ON mydb3.Dest = mydb4.Dest AND
   mydb3.Price = mydb4.Price AND
   mydb3.ID = mydb4.ID

The result I got from this query is as follows:

dest      resort      price      id      type
------------ ------------ ------------ ------------ ------
cancun      hilton      1000.0000      111      star
paris      hilton      1000.0000      666      bird

If this is acceptable, I will then explain what this query is doing, if necessary.  But in summary, this is what it is doing:

1.  Get the lowest price for each destination.
2.  Since it is possible that there will be multiple records having the same lowest price (as is the case with cancun which have 2 rows with 1000 as the lowest price), the row with the lowest ID is retrieved.
3.  Now that we have the rows for each destination with the lowest price and with the lowest ID for multiple lowest price rows, this is now joined with the original table to get all columns where the destination, lowest price and lowest ID is the same.

Hope I was able to explain it clearly.
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13746519
Korr88

SELECT dest, resort, price, id, type
FROM mydb x
WHERE price in (Select Min(price) FROM mydb WHERE dest = x.dest)
Order by lastname

The x distinguishes the mydb table as used by the outer query from the mydb table used in the sub query.

It will still pull duplicates if the lowest price for the same destinition appears more than once.  You haven't stipulated and criteria for choosing which of these duplicates to display.


>And what if i want to get the lowest 10 prices from every destination?

SELECT dest, resort, price, id, type
FROM mydb x
WHERE id in (Select Top 10 id FROM mydb WHERE dest = x.dest ORDER BY price)
Order by dest, price, lastname

Anthony.

0
 

Author Comment

by:korr88
ID: 13770472
ok cool i got rafrancisco's last post working well but for the last shot at this i want to be able to use it but show all the destinations 'dest' lowest price for a departure_airport.
this is the one that works great but only shows only what has already been sorted then shows dallases results, I want to see all of the cheapest destinatins that dallas has.(its got the actual column names this time)damn good work guys thanks.here it is...
SELECT     mydb3.*
FROM         mydb mydb3 INNER JOIN
                          (SELECT     mydb1.destination_airport, mydb1.Price, MIN(mydb1.ID) AS ID
                            FROM          mydb mydb1 INNER JOIN
                                                       (SELECT     destination_airport, MIN(Price) AS Price
                                                         FROM          mydb
                                                         GROUP BY destination_airport) mydb2 ON mydb1.destination_airport = mydb2.destination_airport AND
                                                   mydb1.Price = mydb2.Price
                            GROUP BY mydb1.destination_airport, mydb1.Price) mydb4 ON mydb3.destination_airport = mydb4.destination_airport AND
                      mydb3.price = mydb4.Price AND mydb3.ID = mydb4.ID
WHERE     (mydb3.fixedresortname = 'fixed') and departure_airport = 'Dallas'
order by departure_airport
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774233
Try this:

SELECT     mydb3.*
FROM         mydb mydb3 INNER JOIN
                          (SELECT     mydb1.destination_airport, mydb1.Price, MIN(mydb1.ID) AS ID
                            FROM          mydb mydb1 INNER JOIN
                                                       (SELECT     destination_airport, MIN(Price) AS Price
                                                         FROM          mydb
                                                        WHERE departure_airport = 'Dallas'
                                                         GROUP BY destination_airport) mydb2 ON mydb1.destination_airport = mydb2.destination_airport AND
                                                   mydb1.Price = mydb2.Price
                            GROUP BY mydb1.destination_airport, mydb1.Price) mydb4 ON mydb3.destination_airport = mydb4.destination_airport AND
                      mydb3.price = mydb4.Price AND mydb3.ID = mydb4.ID
WHERE     (mydb3.fixedresortname = 'fixed')
order by departure_airport
0
 

Author Comment

by:korr88
ID: 13775748
almost got it  , half of them are now dallas but there are a few that are not.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13775768
Try this one:

SELECT     mydb3.*
FROM         mydb mydb3 INNER JOIN
                          (SELECT     mydb1.destination_airport, mydb1.Price, MIN(mydb1.ID) AS ID
                            FROM          mydb mydb1 INNER JOIN
                                                       (SELECT     destination_airport, MIN(Price) AS Price
                                                         FROM          mydb
                                                        WHERE departure_airport = 'Dallas'
                                                         GROUP BY destination_airport) mydb2 ON mydb1.destination_airport = mydb2.destination_airport AND
                                                   mydb1.Price = mydb2.Price AND mydb.departure_airport = 'Dallas'
                            GROUP BY mydb1.destination_airport, mydb1.Price) mydb4 ON mydb3.destination_airport = mydb4.destination_airport AND
                      mydb3.price = mydb4.Price AND mydb3.ID = mydb4.ID
WHERE     (mydb3.fixedresortname = 'fixed') AND mydb3.departure_airport = 'Dallas'
order by departure_airport
0
 

Author Comment

by:korr88
ID: 13775926
there was a slight problem but its fixed and works amazing , thanks you made my year. and thanks to everyone else who tried it too. K
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 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