ROW_NUMBER() Problem

Question

ROW_NUMBER() Problem

bvansickle

SSC Rookie

Points: 34
More actions
December 1, 2008 at 12:35 pm

#154970

I've done 95% of my development in MySQL in the past, but I have to use SQL Server 2005 for the project I'm working on now, and I was shocked to find that there is no limit statement...
I understand the Row_Number function can be equivilent, however it's not behaving as I expect it to.
Here is an example query:
SELECT a.ID,b.Name, b.Number, c.ManName,a.IDTag
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC)
as rownum,ID,IDTag,ModelID,ManufacID FROM Machine) AS a
INNER JOIN Model AS b ON a.ModelID=b.ID
INNER JOIN Manufacturer AS c ON a.ManufacID=c.ID
WHERE a.ManufacID='14' AND a.rownum > 0 AND a.rownum < 25
Hopefully you can at least get the idea without seeing the tables. Basically I'm just trying to select some basic information about all products from a given manufacturer. This is working, except my row number values don't seem to be counting only rows from that manufacturer, they seem to be counting everything in my a table. So even though I'm selecting rownum 1-25, i will only get results if there happens to be a manufacID of 14 within the first 25 rows of the table.
I would expect the function to pull all of the results where ManufacID=14 and then order them 1-x for me... do I have a query error? Am I way off base in my expectations?

Viewing 4 posts - 1 through 4 (of 4 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

bvansickle (12/1/2008)
I've done 95% of my development in MySQL in the past, but I have to use SQL Server 2005 for the project I'm working on now, and I was shocked to find that there is no limit statement...
I understand the Row_Number function can be equivilent, however it's not behaving as I expect it to.
Here is an example query:
SELECT a.ID,b.Name, b.Number, c.ManName,a.IDTag
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC)
as rownum,ID,IDTag,ModelID,ManufacID FROM Machine) AS a
INNER JOIN Model AS b ON a.ModelID=b.ID
INNER JOIN Manufacturer AS c ON a.ManufacID=c.ID
WHERE a.ManufacID='14' AND a.rownum > 0 AND a.rownum < 25
Hopefully you can at least get the idea without seeing the tables. Basically I'm just trying to select some basic information about all products from a given manufacturer. This is working, except my row number values don't seem to be counting only rows from that manufacturer, they seem to be counting everything in my a table. So even though I'm selecting rownum 1-25, i will only get results if there happens to be a manufacID of 14 within the first 25 rows of the table.
I would expect the function to pull all of the results where ManufacID=14 and then order them 1-x for me... do I have a query error? Am I way off base in my expectations?

how about:

SELECT a.ID,b.Name, b.Number, c.ManName,a.IDTag

FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ManufacID ORDER BY ID ASC) as rownum,ID,IDTag,ModelID,ManufacID

FROM Machine) AS a

INNER JOIN Model AS b ON a.ModelID=b.ID

INNER JOIN Manufacturer AS c ON a.ManufacID=c.ID

WHERE a.ManufacID='14' AND a.rownum > 0 AND a.rownum < 25

* Noel

bvansickle SSC Rookie Points: 34 More actions · Answer 2

Thanks,

I wasn't 100% understanding what the row_number() function was doing there... I'm all set now.

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 3

You might find that SELECT TOP (xxxx) works pretty well as a sub for the Limit statement, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)