My Query is broken and I don't know how to fix it...

I am trying to set up a filtered paging ObjectDataSource for a gridvoew control.

My code works, my query doesn't...

This is OK: Get all rows of data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes "
Returns all records as expected

This is OK: Getting rows of data with no Filter:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE Row >= 0 AND Row <= 10"
Returns expected data

This is OK: Making sure my WHERE filter works:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%' "
Returns 85 records

This is BROKEN!!!: I want to return 10 rows of filtered data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE '%u%' ) "
It Returns 0 records!!!

What am I doing wrong in the last query?
Or even better how do I fix it?

Thanks,
Roger

Why don't you just do SELECT TOP 10 UCO,...|||

if you don't want use top 10.

The problem is that your where clause needs to be in the derived table.

SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%') As TempRowTable WHERE ( Row >= 0 AND Row <= 10 )

|||

You will have to post a repro script demonstrating the problem. There is nothing wrong with your query. And it is more efficient using the TOP clause in this case to get the top 10 rows instead of using ROW_NUMBER and then filtering on it.

|||Roger,

Your query is most likely working as it should - it should

return 0 rows, given your data, I believe, since you asked

for those rows with country_name like '%u%' that appear in

the first 10 rows of CountryCodes (ordered by country_name).

Apparently none of the first 10 rows have country_name values

like '%u%'.

If you wanted the first 10 rows with country_name like '%u%',

you can either change the problem Ryan noticed (shown here

using a similar query from the AdventureWorks sample database).

select

FirstName, LastName

from (

select

FirstName, LastName,

row_number() over (order by ContactID) as row

from Person.Contact

where LastName like '%x%'

) as C

where row > 0 and row <= 10

go

or use Ryan's and Anith's suggestion of TOP:

select top (10)

FirstName, LastName

from Person.Contact

where LastName like '%x%'

order by ContactID

Note that a query like yours will return no rows here

as well:

select

FirstName, LastName

from (

select

FirstName, LastName,

row_number() over (order by ContactID) as row

from Person.Contact

) as C

where LastName like '%x%'

and row > 0 and row <= 10

go

Steve Kass

Drew University

rogerw@.discussions.microsoft.com wrote:

> I am trying to set up a filtered paging ObjectDataSource for a gridvoew

> control.

>

> My code works, my query doesn't...

>

> This is OK: Get all rows of data:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> CountryCodes "

> Returns all records as expected

>

> This is OK: Getting rows of data with no Filter:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,

> country_code, country_name, enabled, concurrency FROM CountryCodes ) As

> TempRowTable WHERE Row >= 0 AND Row <= 10"

> Returns expected data

>

> This is OK: Making sure my WHERE filter works:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> CountryCodes WHERE country_name LIKE '%u%' "

> Returns 85 records

>

> This is BROKEN!!!: I want to return 10 rows of filtered data:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,

> country_code, country_name, enabled, concurrency FROM CountryCodes ) As

> TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE

> '%u%' ) "

> It Returns 0 records!!!

>

> What am I doing wrong in the last query?

> Or even better how do I fix it?

>

> Thanks,

> Roger

>

>|||

Duh - I thought I had tried that. Must be a clear case of tunnel vision.

Thanks for the help,
Roger