Re: Numbering records in query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Vik (viktorum_at_==hotmail.com==)
Date: 05/20/04 

Date: Thu, 20 May 2004 13:29:05 -0400

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:em5EwiSPEHA.3020@tk2msftngp13.phx.gbl...
> >> How can I create a column with a record [sic] number (1, 2, 3, ...)
> in a SELECT statement with ORDER BY clause? <<
>
> This makes no sense.
>
If this does not make sense to you, it's OK. This made some sense to others
who responded to this question and who posted the Web page with the answers.

> You number lines of a report in the front end, not
> in the database This is the basic idea of a tiered architecture.
>
Did I mention line numbering in my question? I need paging. There may be
other uses of the row numbers.
Did you tell Oracle they don't need ROWNUM because of a tiered architecture?

>
> >> If an ORDER BY field [sic] contains repeated values (e.g. ordering by
> a Product field [sic] and there many records [sic] for each product),
> will the record [sic] order be exactly the same each time I run this
> query (provided the records [sic] do not change)? <<
>
> I think you are asking if SQL requires a stable sort; the answer is NO!
>
I wasn't asking if SQL requires, I was asking if SQL Server provides.
This is MS SQL Server newsgroup and my questions are about SQL Server, not
about RDBMS theory or SQL Standard.

> You need to read a book on SQL and RDBMS;
>
Everybody can read a book instead of posting the questions. Are you
suggesting to close the newsgroups?

> your question is based on a
> mental model that is a sequential file system, not an RDBMS.
>
Mental model is based on a representation of a RDBMS table as a graphical
table. In such a representation, the records are obviously ordered. This
model is not related to the file system. And all this does not mean I don't
know RDBMS.
File system does not matter here. Record order may be maintained in any file
system. Does using a clustered index in SQL Server affect the file system? I
don't think so.

No matter what a mental model is, in SQL Server 6.x, "A SELECT statement
without an ORDER BY clause returned the rows in an apparent ordered set."
(SQL Books). My question was, if SQL Server provides some specific row order
in a case ORDER BY field [sic] is not unique.

> Rows are not records; fields are not columns; tables are not files;
>
Tables may be files, may be not, depending on a product. As regards to rows
and columns, it's a matter of terminology.
Open MS Access, open any table in design view. Notice a *Field* Name column
in the designer. Open the table in normal view. Notice a *Record* label in a
navigation bar at the bottom of the table.
Open "Recordset object" topic in the SQL books. Notice "All Recordset
objects consist of records (rows) and fields (columns)."
Did you tell Microsoft they need to learn RDBMS? What did they answer?

In my OP, a "record number" was not a clear term. That is why I gave an
example ("1,2,3..") of what I need. The "row number" term used in the
replies is definitely better in this case.

> there is no sequential access or ordering in an RDBMS, so "first",
> "next" and "last" are totally meaningless. In other SQL products, the
> PHYSICAL database storage can be re-arranged on the fly without the user
> being told about it.
>
And some products provide the record numbers, which are permanent until a
user rearranges a table.
So, what about SQL Server? Does it rearrange a database without a user
intent?

Viktor Umanskiy

> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: commiting the transaction log?
    ... How much data is stored in memory ... As long as SQL Server has a file open for a long time, ... Is that the reason for the log file (in the file system) having time ... But even if I run the FULL backup, and then the T-log backup, I can see ...
    (microsoft.public.sqlserver.server)
  • Re: dynamically trasnpose rows into columns
    ... > foundation of RDBMS by mixing data and metadata in the schema? ... do you tell them that they have the wrong mental model, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Atachments in File System
    ... If you can afford MOSS 2007 you can afford to use SQL Server. ... If you are looking for the cheapest solution, use WSS 3.0 with its built-in ... I think he wants MOSS 2007 to store attachments in the file system - ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Blob in Access
    ... runs down each file mentioned in the document table verifying ... 2a) It might be nice to store date/time and size of file to see if ... How about SQL Server? ... becomes A451O3 - and stored on the server file system. ...
    (comp.databases.ms-access)
  • Re: FTS and PDFs in row
    ... and the reference the file in the file system and extract its ... Looking for a SQL Server replication book? ... Looking for a FAQ on Indexing Services/SQL FTS ... I've read here and there that storing PDFs in row is frowned upon, ...
    (microsoft.public.sqlserver.fulltext)