Prev: Flat File Import suggestions?
Next: single quotes issue
From: Ray on 28 Jul 2010 13:59
I'm using the OVER Clause with row_number in a view that I have created.

row_number() OVER (ORDER by T.Description) AS 'RN'

What I'm trying to do is get my RN column to display, in sequence the
numbers 1-2-3-4-5-6-7-8 for each row that is selected.

Regardless of what column I put into the ORDER by the number sequence is
anything from 3007-3008-3009 to 75-76-77-78.

Any advice is greatly appreciated.

Cheers

-Ray


From: Plamen Ratchev on 28 Jul 2010 14:04
I am not sure I understand the problem, but if you do not care about
order by any particular column you can write:

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn

--
Plamen Ratchev
http://www.SQLStudio.com
From: Eric Isaacs on 28 Jul 2010 14:10
I'm not sure that I understand your question, but I think you're
saying that you want to order your results by the row_number column.
Just put your row_number in a CTE and then order by the row_number
column, or order by the order by clause from your row_number OVER
clause, or just order by the same row number clause. Here is how you
could do it...

SELECT
RNT.RN,
...
FROM
(SELECT
row_number() OVER (ORDER by T.Description) AS 'RN'
) AS RNT
ORDER BY
RNT.RN

....or...

SELECT
row_number() OVER (ORDER by T.Description) AS 'RN'
ORDER BY
T.Description


....or...

SELECT
row_number() OVER (ORDER by T.Description) AS 'RN'
ORDER BY
row_number() OVER (ORDER by T.Description)


-Eric Isaacs
From: Tom Cooper on 28 Jul 2010 16:15
I'm not entirely sure what you want either, Ray. If Plamen, Eric, and I are
all guessing incorrectly, please provide more detail. My guess is you are
doing something like

Create Table FooTable(ProductName varchar(20), Price decimal(10,2));
go
Create View FooView As
Select ProductName, Price,
Row_Number() Over (Order By Price) As RN
From FooTable;
go
Insert FooTable(ProductName, Price)
Select 'Toy Car', 2.75
Union All Select 'Playing Cards', 5.15
Union All Select 'Toy Airplane', 17.00;

Select ProductName, Price, RN
From FooView
Where ProductName Like 'Toy%'
Order By RN;
go

-- Cleanup
Drop View FooView;
Drop Table FooTable;

That returns two rows, one with row number 1 and one with row number 3. I
suspect that you want a way to the row numbers be one and two. That won't
happen by using the Row_Number() function in the view. The reason is that
given the view definition and the data in the table, 'Toy Airplane' is
associated with RN of 3. And if the where clause in your select statement
that eliminates some rows from the view, won't change which RN value is
associated with which ProductName.

The only way to get sequential numbers beginning with 1 and then in
sequential order no matter which rows in the view are selected, you must use
the Row_Number() function in the Select that queries the view, like:

Select ProductName, Price,
Row_Number() Over (Order By Price) As RN
From FooView
Where ProductName Like 'Toy%'
Order By RN;

Tom

"Ray" <judyscout(a)yahoo.com> wrote in message
news:uZv8R6nLLHA.3668(a)TK2MSFTNGP04.phx.gbl...
> I'm using the OVER Clause with row_number in a view that I have created.
>
> row_number() OVER (ORDER by T.Description) AS 'RN'
>
> What I'm trying to do is get my RN column to display, in sequence the
> numbers 1-2-3-4-5-6-7-8 for each row that is selected.
>
> Regardless of what column I put into the ORDER by the number sequence is
> anything from 3007-3008-3009 to 75-76-77-78.
>
> Any advice is greatly appreciated.
>
> Cheers
>
> -Ray
>
>

From: --CELKO-- on 28 Jul 2010 16:25
>> I am using the OVER Clause with row_number in a view that I have created. ROW_NUMBER () OVER (ORDER by T.somethign_description) AS rn' <<

You need to stop using single quotes on data element names and come up
with better names than "description". It is also a good idea not to
order by a free text column like a description narrative. But
skipping over that:

>> What I'm trying to do is get my rn column to display, in sequence the numbers 1-2-3-4-5-6-7-8 for each row that is selected. <<

Back to SQL fundamentals. A table has no ordering by definition. A
VIEW is a virtual table. Your rn has no effect on what the invoking
query uses for an ordering, unless you EXPLICITLY put it in the
outermost ORDER BY of that query:

CREATE VIEW NamelessView (a,b,c, .., rn)
AS
SELECT a,b,c, ..
ROW_NUMBER() OVER (ORDER BY some_column) AS rn
FROM ..
WHERE ..;

now invoke the view and order it:

SELECT a, b, c, ..
FROM NamelessView
ORDER BY rn ASC;

 |  Next  |  Last
Pages: 1 2
Prev: Flat File Import suggestions?
Next: single quotes issue