Re: Running counter

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/27/04 

Date: Tue, 28 Dec 2004 00:27:35 +0100

On Mon, 27 Dec 2004 13:37:05 -0800, MichaelK wrote:

>Is it possible to make a query to have a counter which would have
>a sequential number of each record in the query starting with 1?

Hi Michael,

That depends. Relational databases handle sets, which are UNordered by
definition, so there is no such thing as an intrinsic "row number". But if
you can define something to base the numbering on, you can have SQL Server
add the numbers.

As an example, the following query will list all authors with a ranking
based on alphabetic order of last name (note how the tie for Albert Ringer
and Ann Ringer is handled!):

USE pubs
GO
SELECT au_id, au_lname, au_fname,
        (SELECT 1 + COUNT(*)
         FROM authors AS b
         WHERE b.au_lname < a.au_lname) AS rank
FROM authors AS a
ORDER BY rank
GO

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Running counter
    ... Regards, ... >>Is it possible to make a query to have a counter which would have ... > based on alphabetic order of last name (note how the tie for Albert Ringer ...
    (microsoft.public.sqlserver.mseq)
  • Re: Table of cells contains comingled text (A,B,C,1,2,3)-formated
    ... > Delimiter As String) As Long ... > Dim Items As Variant ... Each of these fields will look like this in the query ...
    (microsoft.public.access.gettingstarted)
  • Re: WQL Query / Properties order and Limit amount of results
    ... EventID FROM Win32_EventLogFile and the enumerator contain ... EventID, Message, TimeWritten, Type in this alphabetic order. ... number of record the query will catch from ths class? ...
    (microsoft.public.win32.programmer.wmi)
  • Re: Option groups, passing a string value to table
    ... but that is the recommended approach with Relational databases. ... having the words there doesn't improve maintenance: ... query and use the query rather than the table (seldom, if ever, should you ... "Scott" wrote in message ...
    (microsoft.public.access.forms)
  • Re: Queries and OO
    ... Queries is a way to decouple how objects are mapped into a container, ... long time ago there were database types without query ... But maybe the query capabilities was one of the reasons behind the ... success story of relational databases. ...
    (comp.object)