Problem using ROW_NUMBER() OVER () with Sub-Queries

Question

Post reply

Problem using ROW_NUMBER() OVER () with Sub-Queries

Ben Peskoe

Grasshopper

Points: 14
More actions
June 12, 2007 at 2:20 pm

#172666

Hello,
I'm using the following Query in SQL Server 2005. It is set up to Order by the Price field, which is type Money:
SELECT ListingID, MLSRegion_ID, Price, ListType, FeaturesGarage, County, City, ListTypeDescrip, Latitude, Longitude, Geocode_ID
, Photo, NumPhotos
FROM (SELECT m.ID AS ListingID, m.MLSRegion_ID, m.Price, m.ListType, m.FeaturesGarage, m.County, m.City, m.ListTypeDescrip
, g.Latitude, g.Longitude, g.ID AS Geocode_ID
, (SELECT TOP 1 p.Photo FROM MLSPhotos p WHERE p.MLS_ID = m.ID ORDER BY p.ID) AS Photo
, (SELECT COUNT(p.ID) FROM MLSPhotos p WHERE p.MLS_ID = m.ID) AS NumPhotos
, ROW_NUMBER() OVER (ORDER BY Price) AS RowNum
FROM MLS m
LEFT OUTER JOIN Geocodes g ON Geocode_ID = g.ID
WHERE (MLSRegion_ID IN (10)) AND (ListType IN (1)) AND (Price >= 500000)
AND (Price <= 2200000) AND ((FeaturesGarage LIKE ('%3%')) OR (FeaturesGarage LIKE ('%4%')))
AND m.County IN ('la') AND m.City IN ('cerritos ') AND m.ListTypeDescrip IN ('single family residence'))
AS MLSListings WHERE RowNum BETWEEN 1 AND (10)
But the results do not order by Price. Instead they are returned in no discernible order.
However, when I remove the Photos and NumPhotos fields from the first select statement (which are generated by sub-queries in the second select statement), the results are returned in the proper order with no problems.
Can anyone tell me what is causing this? Any thoughts on how to fix?
Many thanks!
Ben

Viewing 10 posts - 1 through 9 (of 9 total)

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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

The order by in the Row_number() is for assigning values for the RowNum column.

You now have a table an additional column rowNum assigned a value by the order by.

An order by for the actual query return is still required.

Ben Peskoe Grasshopper Points: 14 More actions · Answer 2

Thanks for your reply

Unfortunately, I'm not quite following

I thought that the ROW_NUMBER() OVER (ORDER BY ...) statement was meant to take the place of an order by for the actual query return? Meaning that I'm actually ordering by the RowNum column that's created in the query.

Why is this affected when a sub-query is added?

Thanks again

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

If all you want is to order the result set by the price, do that, not use the row_number() function.

Ben Peskoe Grasshopper Points: 14 More actions · Answer 4

I'm using Row_Number() because I am dealing with a large number of records and only want to return the specific records in question

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 5

Row_number is populating column rowNum.

In your where statement add [Where rowNum = 1]. This if designed correctly will limit you result set to only the data you want.

Once you have your result set you can then order it.

Ben Peskoe Grasshopper Points: 14 More actions · Answer 6

Thanks, but isn't that the point of using ROW_NUMBER() OVER (ORDER by m.Price ASC) in the first place -- to get the Rows in the proper order at the time when the RowNum Column is created?

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 7

row_number is designed to insert a column that is an integer (pseudo identity) that you can use to get only the latest, last 3, last 10...

I use it myself for getting my latest barcode scan.

good luck

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 8

I just love row_number() so i will go on... (and on...)

Example of Row_number()

To find the average value of the last three listings/offers by house/listingid

declare @HouseList table(id INT IDENTITY, listingID INT, regionID int, Price float, listingDate DATETIME)

INSERT INTO @houseList (listingID, regionID, price, listingDate)

SELECT 1, 1, 140000, '2007/05/01' UNION ALL

SELECT 1, 1, 120000, '2007/05/05' UNION ALL

SELECT 1, 1, 110000, '2007/06/05' UNION ALL

SELECT 1, 1, 105000, '2007/07/05' UNION ALL

SELECT 1, 1, 100000, '2007/08/05' UNION ALL

SELECT 2, 4, 255000, '2007/08/04'

SELECT listingID, AVG(price)

FROM

( SELECT id, listingid, regionid, price, listingDate

, ROW_NUMBER() OVER (PARTITION BY listingID ORDER BY listingDate desc) AS RowNum

FROM @houseList

) orderedHouseList

WHERE rowNum < 4

GROUP BY listingID

Anders Pedersen SSChampion Points: 11410 More actions · Answer 9

The Row_Number is in the right order, but not necessarily the order that the data is returned. For that you still need an order by after the where clause.