grogo21
asked on
Using ROW_NUMBER()
Hello Can I use the Row_Number function with a select that joins tables? Im having trouble figuring how to write the select using Row_Count using this select:
SELECT c.ItemId, c.Title, a.Fname, a.Lname, c.ItemType, c.ImageId, c.Price
from Catalog c, ItemCat i, Artist a
where c.ItemId = i.ItemId And a.ArtistId = c.ArtistId And i.SubCatId = @subcatid
Thanks Very Much!
SELECT c.ItemId, c.Title, a.Fname, a.Lname, c.ItemType, c.ImageId, c.Price
from Catalog c, ItemCat i, Artist a
where c.ItemId = i.ItemId And a.ArtistId = c.ArtistId And i.SubCatId = @subcatid
Thanks Very Much!
Nerdwood
Are you meaning to use @@ROWCOUNT to find the number of records affected?
Are you wanting something like this? What's the row count for?
SELECT ROW_NUMBER() OVER (ORDER BY c.ItemId) AS 'Row Number', c.ItemId, c.Title, a.Fname, a.Lname, c.ItemType, c.ImageId, c.Price
FROM Catalog c, ItemCat i, Artist a
WHERE c.ItemId = i.ItemId And a.ArtistId = c.ArtistId And i.SubCatId = @subcatid
ASKER
Hello, I am trying to do something like below to implement paging of results:
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10
Thanks
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10
Thanks
That's not working?
Is there a problem? My favorite:
With MyCTE
AS
(SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) Row, Description, Date FROM LOG)
SELECT * from MyCTE
WHERE Row >= 1 AND Row <= 10
With MyCTE
AS
(SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) Row, Description, Date FROM LOG)
SELECT * from MyCTE
WHERE Row >= 1 AND Row <= 10
ASKER
Im sorry, that is an example seen on this page http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
Im having trouble implementing ROW_NUMBER like he did in the link with the select statement I posted in my original post becuase it has joins in it.
Thanks
Im having trouble implementing ROW_NUMBER like he did in the link with the select statement I posted in my original post becuase it has joins in it.
Thanks
Try this:
SELECT c.ItemId, c.Title, a.Fname, a.Lname, c.ItemType, c.ImageId, c.Price
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY c.Price) AS Row, c.ItemId, c.Title, a.Fname, a.Lname, c.ItemType, c.ImageId, c.Price
FROM (Catalog c
INNER JOIN ItemCat i ON c.ItemId = i.ItemId)
INNER JOIN Artist a ON c.ArtistId = a.ArtistId
WHERE i.SubCatId = @subcatid
)
WHERE Row BETWEEN 1 AND 10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excellent... good to know it worked! :)