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.
| Author |
Topic |
|
cls2003
Starting Member
1 Post |
 Posted - 2008-12-01 : 14:30:56
|
| 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 < 25Hopefully 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? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-12-01 : 15:15:01
|
There is no WHERE-clause in your derived table named a.So row_number is working on all records in table machine.Btw. to get only 25 rows you can use select top 25 Id, Name, ... from table order by ... Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-01 : 23:55:31
|
| [code]SELECT a.ID,b.Name, b.Number, c.ManName,a.IDTag FROM (SELECT ROW_NUMBER() OVER(PARTITION BY a.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[/code] |
|
|
|
|
|
|
|
|
ROW_NUMBER() Problems