Getting Row number in 'SELECT'.

Deepak Kini M

Getting Row number in 'SELECT'.
Dear List,

How does one return the row count with the results of the query? For
example:

SELECT firstname, lastname WHERE lastname=`Smith`;

Results:
1 John Smith
2 Sam Smith
3 Steve Smith

It`s the 1, 2, 3... column that I`m looking to produce. I`m aware of
how auto_increment works, but I don`t feel that would be appropriate in
this instance, since the field that has the auto_increment may have
nothing to do with the sequence of a conditional query.

Regards
Deepak


Steen Rasmussen

Re: Getting Row number in 'SELECT'.
(in response to Deepak Kini M)
Select firstname,lastname,count(*)
from table
where lastname='smith'
group by firstname,lastname
order by 3,1,2 ;



Kind regards
Steen Rasmussen
Computer Associates


-----Original Message-----
From: Deepak Kini M [mailto:[login to unmask email]
Sent: 11. januar 2002 10:15
To: [login to unmask email]
Subject: Getting Row number in 'SELECT'.


Dear List,

How does one return the row count with the results of the query? For
example:

SELECT firstname, lastname WHERE lastname=`Smith`;

Results:
1 John Smith
2 Sam Smith
3 Steve Smith

It`s the 1, 2, 3... column that I`m looking to produce. I`m aware of
how auto_increment works, but I don`t feel that would be appropriate in
this instance, since the field that has the auto_increment may have
nothing to do with the sequence of a conditional query.

Regards
Deepak


Damien Blood

Re: Getting Row number in 'SELECT'.
(in response to Steen Rasmussen)
Good! Also adding to the solution:

A count of the id or table primary key
would speed up the query as the column
is indexed i.e.

Select firstname,lastname,count(id)
from table
where lastname='smith'
group by firstname,lastname
order by 3,1,2 ;


-----Original Message-----
From: Rasmussen, Steen [mailto:[login to unmask email]
Sent: 11 January 2002 09:49
To: [login to unmask email]
Subject: Re: Getting Row number in 'SELECT'.


Select firstname,lastname,count(*)
from table
where lastname='smith'
group by firstname,lastname
order by 3,1,2 ;



Kind regards
Steen Rasmussen
Computer Associates


-----Original Message-----
From: Deepak Kini M [mailto:[login to unmask email]
Sent: 11. januar 2002 10:15
To: [login to unmask email]
Subject: Getting Row number in 'SELECT'.


Dear List,
How does one return the row count with the results of the query? For
example:
SELECT firstname, lastname WHERE lastname=`Smith`;
Results:
1 John Smith
2 Sam Smith
3 Steve Smith
It`s the 1, 2, 3... column that I`m looking to produce. I`m aware of
how auto_increment works, but I don`t feel that would be appropriate in
this instance, since the field that has the auto_increment may have
nothing to do with the sequence of a conditional query.
Regards
Deepak


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Should you require further information please contact
us using the details below.

website: http://www.acuma.co.uk
email: [login to unmask email]
**********************************************************************



Dave Nance

Re: Getting Row number in 'SELECT'.
(in response to Damien Blood)
These suggestions will give you a count of how many times that name occurred. There isn't a very smooth way of doing this within your SQL. Is this sql being executed by a program, QMF, etc..? If so, there are a multitude of easy ways to accomplish. Otherwise, here's an example that was used on the list awhile back:

SELECT
COUNT (*),
T2.SALES_CODE
FROM
(SELECT SALES_CODE FROM VWRS.TBSCEQUV) AS T1
, VWRS.TBSCEQUV T2
WHERE
T1.SALES_CODE < T2.SALES_CODE
GROUP BY
T2.SALES_CODE




>>> [login to unmask email] 01/11/02 05:21AM >>>
Good! Also adding to the solution:

A count of the id or table primary key
would speed up the query as the column
is indexed i.e.

Select firstname,lastname,count(id)
from table
where lastname='smith'
group by firstname,lastname
order by 3,1,2 ;


-----Original Message-----
From: Rasmussen, Steen [mailto:[login to unmask email]
Sent: 11 January 2002 09:49
To: [login to unmask email]
Subject: Re: Getting Row number in 'SELECT'.


Select firstname,lastname,count(*)
from table
where lastname='smith'
group by firstname,lastname
order by 3,1,2 ;



Kind regards
Steen Rasmussen
Computer Associates


-----Original Message-----
From: Deepak Kini M [mailto:[login to unmask email]
Sent: 11. januar 2002 10:15
To: [login to unmask email]
Subject: Getting Row number in 'SELECT'.


Dear List,
How does one return the row count with the results of the query? For
example:
SELECT firstname, lastname WHERE lastname=`Smith`;
Results:
1 John Smith
2 Sam Smith
3 Steve Smith
It`s the 1, 2, 3... column that I`m looking to produce. I`m aware of
how auto_increment works, but I don`t feel that would be appropriate in
this instance, since the field that has the auto_increment may have
nothing to do with the sequence of a conditional query.
Regards
Deepak


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Should you require further information please contact
us using the details below.

website: http://www.acuma.co.uk
email: [login to unmask email]
**********************************************************************




This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.



Colin Clayton

Re: Getting Row number in 'SELECT'.
(in response to Dave Nance)
Damien,

have you run that SQL.

If you did, I suspect you would have got a "-409, ERROR: INVALID OPERAND OF
A COUNT FUNCTION " message.

The only valid operands of a COUNT function are '*' or 'DISTINCT expression'

Anyway, I believe that Deepak's original question was how to add row numbers
to the result, not a count of how many distinct values.

Colin