SQL Guru's - please help paging with a distinct recordset

8 Sep 2006 12:19 AM

PepperFleming@gmail.com

Hi Guru's,

I'm trying to use SQL Server 2005's new Row_Number() function to make
my life paging data easier, but have run into a little propblem trying
to page a set of distinct values.

Here is my query:

Select distinct city, Row_Number() over (order by City) as RowNumber
from MemberData

this returns the complete table of data with cities duplicated.

The first attempt was:

select distinct city from (Select city, Row_Number() over (order by
city) as RowNumber from MemberData) as returnset where RowNumber >= 1
and RowNumber <= 15

Can someone point me in the right direction to get a distinct set of
data that is ordered and usable by RowNumber? Using a temp table isn't
a great option for me in this case since it is a web enviroment.

Thanks,

Pepper

8 Sep 2006 12:37 AM

Roy Harvey

You want to apply the DISTINCT before anything else. This can be done
in a derived table. This worked against Adventureworks:

SELECT City,
ROW_NUMBER() OVER(ORDER BY City) AS RowNbr
FROM (select distinct City from Person.Address) as D

Roy Harvey
Beacon Falls, CT

On 7 Sep 2006 17:19:34 -0700, "PepperFlem***@gmail.com"
<PepperFlem***@gmail.com> wrote:

Show quote

>Hi Guru's,
>
>I'm trying to use SQL Server 2005's new Row_Number() function to make
>my life paging data easier, but have run into a little propblem trying
>to page a set of distinct values.
>
>Here is my query:
>
>Select distinct city, Row_Number() over (order by City) as RowNumber
>from MemberData
>
>this returns the complete table of data with cities duplicated.
>
>The first attempt was:
>
>select distinct city from (Select city, Row_Number() over (order by
>city) as RowNumber from MemberData) as returnset where RowNumber >= 1
>and RowNumber <= 15
>
>Can someone point me in the right direction to get a distinct set of
>data that is ordered and usable by RowNumber? Using a temp table isn't
>a great option for me in this case since it is a web enviroment.
>
>Thanks,
>
>Pepper

8 Sep 2006 12:51 AM

Steve Dassin

SELECT City,ROW_NUMBER() OVER(ORDER BY City) AS RowNbr
FROM Person.Address
GROUP BY City

http://racster.blogspot.com

8 Sep 2006 1:22 AM

PepperFleming@gmail.com

Thanks Guys, I really appreciate the help.

8 Sep 2006 4:48 AM

Arnie Rowland

Fun and games and fest,
Steve provided help to a quest
Plug RAC not the best

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Steve Dassin" <steve@nospamrac4sql.net> wrote in message
news:OsWWtEu0GHA.4392@TK2MSFTNGP04.phx.gbl...
> SELECT City,ROW_NUMBER() OVER(ORDER BY City) AS RowNbr
> FROM Person.Address
> GROUP BY City
>
> http://racster.blogspot.com
>
>
>

8 Sep 2006 6:35 PM

Steve Dassin

"Arnie Rowland" <ar***@1568.com> wrote in message
news:OpiAKIw0GHA.4448@TK2MSFTNGP04.phx.gbl...
> Fun and games and fest,
> Steve provided help to a quest
> Plug RAC not the best

Touche :)