Where i am making mistake..?

Question

Where i am making mistake..?

ahmed.net

Mr or Mrs. 500

Points: 557
More actions
November 11, 2010 at 2:04 am

#242784

My query is
"SELECT [ResultID]
,[HotelSearchResults].[HotelID]
,[HotelName]
,p.minPrice
FROM [Hotels].[dbo].[HotelSearchResults]
INNER JOIN
(SELECT HotelID, MIN(Price) AS minPrice
FROM HotelSearchResults
GROUP BY HotelID)p ON HotelSearchResults.HotelID = p.HotelID"
Now HotelSearchResults has multiple rows with same [HotelID], i want to get Rows with the Minimum [Price] for earch [HotelID].
How can i do this?
Where i am making mistake..?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It's because you've got more than one ResultID for each hotel. Does it matter which one you display? You can use GROUP BY and show the mininum or maximum value per hotel it that gets you what you require.

John

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 2

i guess you have more than one hotelid in one of the table as your results shows diffrent resultid with same records

ahmed.net Mr or Mrs. 500 Points: 557 More actions · Answer 3

Thanks John

I want to get only one Row per Hotel, a row that has the minimum Price.

I am already using group by but is not working properly.

plz view the attached picture with my last post

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

In your sample results, the price is the same where the hotel is the same. But you can still group by the hotel name to show the minimum price - just means you can't display the ResultID unless you join back to the original table.

John

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

You probably forgot a JOIN condition on the Price Column

SELECT[ResultID], [HotelSearchResults].[HotelID], [HotelName], P.minPrice

FROM[Hotels].[dbo].[HotelSearchResults]

INNER JOIN(

SELECTHotelID, MIN(Price) AS minPrice

FROMHotelSearchResults

GROUP BY HotelID

) P

ON HotelSearchResults.HotelID = p.HotelID

AND HotelSearchResults.Price = P.minPrice -- You missed this Join

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ahmed.net Mr or Mrs. 500 Points: 557 More actions · Answer 6

Thanks Kingston Dhasian

Problem solved.

im a stupid.....:hehe:

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

Such mistakes happen sometimes. So no problems. Glad i could help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/