Re: Running counter

From: Derrick Leggett (derrickleggett_at_yahoo.com)
Date: 12/28/04 

Date: Mon, 27 Dec 2004 19:04:34 -0600

Insert the records into a table variable or temp table (if more than 10k
records) that includes an identity column. The query selects the final
resultset. How big of a recordset are you talking about here? Why do you
need to do this?

"MichaelK" <michaelk@gomobile.com> wrote in message
news:esS9uFH7EHA.128@TK2MSFTNGP15.phx.gbl...
> Thanks Hugo.
> Interesting idea and works, but takes very long.
> Unfortunately can't use it, need something faster.
>
> Hey guys any other ideas?
>
> Regards,
> Michael
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:q961t0l1ohanli4b68a4j71d4vet8t8j72@4ax.com...
> > 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: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)
  • Re: data type error in my query
    ... the resultset returned the same value for comments. ... FROM dbo.tblWeblog as W1 LEFT OUTER JOIN dbo.tblUserComments as C ... > If blogID is a unique identifier for rows of tblWeblog, ... If I strip out W.blogComment the query runs fine. ...
    (microsoft.public.sqlserver.programming)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • refreshing a window
    ... the query is processed, the results do not come up at the bottom until ... private JTextField output, input, input1, input2, input3; ... ResultSet resultSet; ...
    (comp.lang.java.programmer)
  • Re: List Box Display Anomaly
    ... Michael, since it works fine on one machine but not on the other, you are looking to pin down the difference. ... There were some bugs introduced by SP3 for Office 2003, including combo display problems: ... All of a sudden, the columns from one of the tables is not displaying in the list box, but if I run the query in the query window, all the columns return data. ... I delivered a new front-end to a client this morning and he alerted me the anomaly. ...
    (microsoft.public.access.forms)