sql query problem with ROW_NUMBER()

Question

0.00/5 (No votes)

See more:

here the "where" not work:

with OrderedOrders AS
(
select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' 
from dbo.CountryAlbums  where SubCategoryName = 'Rockabilly'              
)   
select * from OrderedOrders where RowNumber between 5 and 10

and after i take all the table the "where" is work:

with OrderedOrders AS
(
select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' 
from dbo.CountryAlbums              
)   
select * from OrderedOrders where RowNumber between 5 and 10  and SubCategoryName = 'Rockabilly'

I do not want to pull the entire table How can I register it as the first example?

Posted 5-Dec-14 23:27pm

Member 10631195

Add a Solution

Comments

OriginalGriff 6-Dec-14 5:45am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
The second query will nor produce anything like the first, and it's not at all obvious what you want to happen.
Use the "Improve question" widget to edit your question and provide better information.

Maciej Los 6-Dec-14 6:37am

What you mean by "not work". Please, post sample data and expected output. Use "Improve question" widget!

Member 10631195 6-Dec-14 7:12am

see the problem :
http://i.imgur.com/wKKxDSZ.png

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-12-06T00:43:00

Solution 2

This query:

SQL

;with OrderedOrders AS
(
    select * , ROW_NUMBER() over(order by AlbumName) as 'RowNumber' 
    from dbo.CountryAlbums
    where SubCategoryName = 'Rockabilly'              
)   
select *
from OrderedOrders
where RowNumber between 5 and 10

is well written and has the same meaning as:

SQL

SELECT *
FROM (
    SELECT * , ROW_NUMBER() OVER(ORDER BY AlbumName) AS 'RowNumber' 
    FROM dbo.CountryAlbums
    WHERE SubCategoryName = 'Rockabilly'              
) AS T
WHERE RowNumber between 5 and 10

You should be more specific when you saying: "this or that is not working"!

Posted 6-Dec-14 0:43am

Maciej Los

v2

Comments

Member 10631195 6-Dec-14 6:57am

i meen the "where" condition dosent work just after i take all the table

It still does not work but thanks just the way it works:
I think it is wrong to work so what do you think?

SELECT *
FROM (
SELECT * , ROW_NUMBER() OVER(ORDER BY AlbumName) AS 'RowNumber'
FROM dbo.CountryYoutubeAlbums
) AS T
WHERE RowNumber between 5 and 10 and SubCategoryName = 'Rockabilly'

Maciej Los 6-Dec-14 7:03am

I have no idea, because i don't get you ;(
Still don't know, what you mean "not work"!

Member 10631195 6-Dec-14 7:12am

see the problem :
http://i.imgur.com/wKKxDSZ.png

Maciej Los 6-Dec-14 7:34am

Remove WHERE statement from the first query and check what it returns. I believe there are only 2 rows with row numbers 1 and 2. So, how it's possible to return rows with rownumbers between 5 and 10?

Member 10631195 6-Dec-14 8:33am

i get all the table and rom there i take 5-10 result i add the ROW_NUMBER()
because i wont in my website to do paging

mudgilsks · Answer 2 · 2014-12-05T23:54:00

Solution 1

hi,

try this

SQL

select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' into #OrderedOrders  from dbo.CountryAlbums  where SubCategoryName = 'Rockabilly'

select * from #OrderedOrders where RowNumber between 5 and 10
drop table #OrderedOrders

Posted 5-Dec-14 23:54pm

mudgilsks

Comments

Member 10631195 6-Dec-14 6:52am

I tried it but it does not work

Member 10631195 6-Dec-14 6:54am

and just after a get all the table i can , see:
select * , ROW_NUMBER() over (order by AlbumName) as 'RowNumber' into #OrderedOrders from dbo.CountryAlbums

select * from #OrderedOrders where RowNumber between 5 and 10 and SubCategoryName = 'Rockabilly'
drop table #OrderedOrders

Member 10631195 6-Dec-14 7:12am

see the problem :
http://i.imgur.com/wKKxDSZ.png

mudgilsks 6-Dec-14 8:25am

hi,

Problem is that when you use execute the below query

select * , ROW_NUMBER() over (order by AlbumName) as 'RowNumber' into #OrderedOrders from dbo.CountryAlbums where SubCategoryName = 'Rockabilly'

it returns 2 record with sr no 1 and 2 because you have only 2 row contains SubCategoryName name as Rockabilly and in 2nd query you have give RowNumber between 5 and 10 ,so only RowNumber between 1 and 2 will come.

try this