Where i am making mistake..? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where i am making mistake..?

Author

Topic

ishti123
Starting Member

5 Posts

Posted - 2010-11-11 : 06:27:06

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..?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 06:37:12

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 and HotelSearchResults.price=p.minprice

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-11 : 06:46:17

or:


SELECT ResultID, HotelID, HotelName, Price
FROM
(
	SELECT ResultID, HotelID, HotelName, Price
		,RANK() OVER (PARTITION BY HotelID ORDER BY Price) AS Ord
	FROM HotelSearchResults
) D
WHERE Ord = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources