views:

39

answers:

2

Hi!

I've been browsing the forums a while trying to find a solution to my issue.

I have a table in SQL Server 2000 called result with the following data;

City       Price DepDate     RetDate
Barcelona  145   2010-05-15  2010-05-20
New York   400   2010-06-20  2010-06-20
Barcelona  160   2010-05-17  2010-05-22
New York   325   2010-05-10  2010-05-18
Istanbul   250   2010-06-22  2010-06-27

I want to query that in SQL Server 2000 so that all columns are returned but it's distinct/uniqe on the city and only the lowest price is displayed.

So i would like it to return the following

Barcelona  145   2010-05-15  2010-05-20
New York   325   2010-05-10  2010-05-18
Istanbu    250   2010-06-22  2010-06-27

I cant for my life figure out how to do this, i should be easy and I'm sure I'm going to feel dumb when the solution is presented.

Does anyone know how to do this?

Brgds, Eric

+1  A: 
SELECT
    A1.*
FROM
    [TableName] A1
    INNER JOIN 
    (
        SELECT
            A.City
            ,   A.Price
            ,   MIN(A.DepDate) AS DepDateMin
        FROM
            [TableName] A
            INNER JOIN
            (   SELECT
                    City
                    ,   MIN(Price) AS PriceMin
                FROM
                    [TableName]
                GROUP BY City
            ) B ON A.City = B.City AND A.Price = B.PriceMin
        GROUP BY 
            A.City, A.Price
    ) B1 ON A1.City = B1.City AND A1.Price = B1.PriceMin AND A1.DepDate = B2.DepDateMin
rdkleine
Need modification in your query to give alias for MIN(Price) and condition should be A.price = B.<price aliasName>, after that, this query will work
Sachin Shanbhag
It Works! Thank you so much for this, hopefully I'll understand what's making it work as well. I've been at this for ages. Thank you!!
Eric Bruno
One quick question, is it easy to modify this so that the price would have to be distinct as well?
Eric Bruno
@Sachin, added the alias.
rdkleine
@Eric, when there are two (or more) records with identical City and Price are the dates different or the same? When the same use DISTINCT.
rdkleine
The Dates would be different. Like this:Barcelona 145 2010-05-15 2010-05-20Barcelona 145 2010-05-10 2010-05-15Even if i ran the select above, it would display both results instead of only one of them, right? Can I tweek it to show only one of them, say the one with the earliest DepDate?
Eric Bruno
@Eric, updated the query
rdkleine
Hmm, problem seems that for this to work, the lowest price, MUST be on the earliest date for that destination. Otherwise the record will not be displayed at all. Say the lowest price occures on a later date for some destination. Then that will not be displayed.
Eric Bruno
@Eric, update the query
rdkleine
That's it :DThank you very much. I think it's time for me to learn a bit more about joins and such :)
Eric Bruno
You should ;) It will come in handy. btw mark the answer as answered when it does, helps other ppl with same sort of question
rdkleine
A: 

try this one

select City,Price,DepDate,,RetDate from Your_table a 
where a.Price = (select min(Price) from Your_table b 
                where a.city = b.city group by city)
Bharat
This seems to work as well :)
Eric Bruno