Prev: Flat File Import suggestions?
Next: single quotes issue
From: ray on 28 Jul 2010 17:13
Plamen, Eric, Tom and --CELKO--

Thank you all for your great suggestions. We all know that when we add an
identity column to a table, seed it with 1 each row is numbered sequentially
1 to n with a unique number.

For clarity sake. Here's what I was attempting to accomplish and thought
"maybe" OVER clause was the way to go.

1. I have a table with 1 million rows.
2. I'm using a view to grab a subset of rows from that table.
3. I want a Record Number Column (Thats why the use on OVER clause) to
reflect a record "sequence" for this virtual table.

eg:

Name State Zip Record Number

Abby MO 44765 1
Schmidt NJ 07846 2
Thomas FL 33756 3
Zappa CA 92390 4

4. I "thought" - now I could be totally wrong and mistaken on my approach
here and am willing to eat crow." That....
I could then use this Record Number column when runing a Select
statement on the View using.. Where [Record Number] <=3

I do hope this clarifies it.

Again, I'm totally open to any and all corrections, help etc

Now to address Mr. --CELKO.--

I first and formost should have clarified exactly what I was trying to do
which was simply selecting a subset of records by use of a view. The view
would have a column filled with an Int sequencing each row starting with
1-2-3-4-5...n.

1. "Description" was for demo purposes, my column names are pretty much
alien in nature or greek derivations so as not to bump into any SQL reserved
words.

2. Your right I should be carefull in my use of Single Quotes.

3. unless you EXPLICITLY put it in the > outermost ORDER BY of that query:
YEP, that was done.

4. now invoke the view and order it: Yes, thats pretty fundamental on the
ordering by RN.

Thanks again.

Semper Fidelis

-Ray


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:f5a3d537-96d5-408f-a57f-9a7cc8ea2206(a)g35g2000yqa.googlegroups.com...
>>> 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;
>


From: Eric Isaacs on 28 Jul 2010 17:47
> eg:
>
>     Name          State             Zip            Record Number
>
>     Abby           MO              44765                 1
>     Schmidt       NJ                07846                 2
>     Thomas       FL                 33756                3
>     Zappa         CA                92390                4
>
> 4.  I "thought" - now I could be totally wrong and mistaken on my approach
> here and am willing to eat crow." That....
>      I could then use this Record Number column when runing a Select
> statement on the View using.. Where [Record Number] <=3

You thought correctly.

SELECT * FROM ViewNameWHERE [Record Number] <=3

SELECT * FROM ViewNameWHERE [Record Number] BETWEEN 4 and 6

SELECT TOP 3 FROM ViewName ORDER BY [Record Number]

....are all valid ways to select by record number from the view. I
would however suggest that you do not include the record number in the
view, but rather do it outside of the scope of the view so that you
have more control and can use a WHERE clause from the view to reduce
or control which rows you get back. Do that logic from the stored
procedure so that it's more reusable.

-Eric Isaacs
From: Erland Sommarskog on 28 Jul 2010 18:21
ray (judy(a)yahoo.com) writes:
> 1. I have a table with 1 million rows.
> 2. I'm using a view to grab a subset of rows from that table.
> 3. I want a Record Number Column (Thats why the use on OVER clause) to
> reflect a record "sequence" for this virtual table.
>
> eg:
>
> Name State Zip Record Number
>
> Abby MO 44765 1
> Schmidt NJ 07846 2
> Thomas FL 33756 3
> Zappa CA 92390 4
>
> 4. I "thought" - now I could be totally wrong and mistaken on my approach
> here and am willing to eat crow." That....
> I could then use this Record Number column when runing a Select
> statement on the View using.. Where [Record Number] <=3

And that isn't happening?

Nothing you says here seems very strange, but I don't understand where you
numbering in your original post comes in.

Any chance that you can produce a script with a CREATE TABLE, a CREATE VIEW
and a query that demonstrates your issue? It does not have to be the real
McCoy, just something simple.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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