Getting only top 1 from joined table into recordse

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 2000 Forums

Transact-SQL (2000)

Getting only top 1 from joined table into recordse

Author

Topic

julianfraser
Starting Member

19 Posts

Posted - 2005-05-12 : 13:25:00

I need to get the following recordset, but instead of all rows in the joined table that correspond to the propertyID in the main table, I want only the TOP 1 rows for each propertyID, so that each propertyID only appears once in the recordset and the top photoID corresponding to the list_order column in the property_photo table is shown.

I know this is probably really simple, I just can't seem to work it out.

SELECT rt.propertyID, photoID, title, property_type, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension 	
FROM #ResultsTable rt
LEFT JOIN property_photo pp ON pp.propertyID = rt.propertyID
WHERE (row_num >= @row_start) AND (row_num <= @row_end)

Thanks,
Julian.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-12 : 13:34:07

Would this work


SELECT rt.propertyID, MAX(photoID), title, property_type, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension 	
FROM #ResultsTable rt
LEFT JOIN property_photo pp ON pp.propertyID = rt.propertyID
WHERE (row_num >= @row_start) AND (row_num <= @row_end)
GROUP BY SELECT rt.propertyID, title, property_type, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension

Andy

Beauty is in the eyes of the beerholder

julianfraser
Starting Member

19 Posts

Posted - 2005-05-12 : 13:37:24

Worked great thanks... i was having a blonde moment I guess... been a long day here!

Thanks again,
Julian.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources