Re: Getting Row number in 'SELECT'.

Ruediger Kurtz

Re: Getting Row number in 'SELECT'.
(in response to Mohammad Khan)
Hmmmm, doesn't this give

adam smith 1
benny smith 1
clark smith 1
....

Regards Ruediger Kurtz

> "Rasmussen, Steen" schrieb:
>
> 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

--
>"""""<
!_ _!
(o) (o)
oOOO--(_)--OOOo------------------------------------------------------*
* *
* Rüdiger Kurtz *
* *
* HUK-Coburg Phone: 09561/96-3914 *
* Versicherungen-Bausparen Fax : 09561/96-3678 *
* Abt. Informatik - Betrieb (Datenbank-Administration) *
* Bahnhofsplatz Mail: [login to unmask email] *
* 96450 Coburg *
* *
* .oooO *
*--( )--Oooo.------------------------------------------------------*
\ ( ( )
\_) ) /
(_/



[login to unmask email]

Re: Getting Row number in 'SELECT'.
(in response to DB46@DAIMLERCHRYSLER.COM)
This won't work. You will get 1 for a count unless somebody has the same
first and last name.

Dean




Damien Blood
<[login to unmask email] To: [login to unmask email]
UMA.CO.UK> cc:
Sent by: DB2 Subject: Re: Getting Row number in 'SELECT'.
Data Base
Discussion List
<[login to unmask email]>


01/11/02 05:21
AM
Please respond
to DB2 Data Base
Discussion List






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]
**********************************************************************












****************************************************************************

The information contained in this transmission, which may be
confidential and proprietary, is only for the intended recipients.
Unauthorized use is strictly prohibited. If you receive this
transmission in error, please notify me immediately by telephone
or electronic mail and confirm that you deleted this transmission
and the reply from your electronic mail system.
****************************************************************************



[login to unmask email]

Getting Row number in 'SELECT'. choice 2
This won't work also




"Rasmussen,
Steen" To: [login to unmask email]
<Steen.Rasmuss cc:
[login to unmask email]> Subject: Re: Getting Row number in 'SELECT'.
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
M>


01/11/02 04:49
AM
Please respond
to DB2 Data
Base
Discussion
List






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























****************************************************************************

The information contained in this transmission, which may be
confidential and proprietary, is only for the intended recipients.
Unauthorized use is strictly prohibited. If you receive this
transmission in error, please notify me immediately by telephone
or electronic mail and confirm that you deleted this transmission
and the reply from your electronic mail system.
****************************************************************************



Terry Purcell

Re: Getting Row number in 'SELECT'.
(in response to DB46@DAIMLERCHRYSLER.COM)
Colin,

SELECT COUNT(expression) is a feature of V7, and supplements COUNT(*) &
COUNT(DISTINCT expression).

COUNT(*) - counts all qualifying rows
COUNT(DISTINCT expression) - counts unique occurrences of "expression" with
duplicates eliminated.
COUNT(expression) - counts all occurrences of "expression" with duplicates
eliminated.

Damien,

As David stated, there aren't any good performing "SQL only" solutions to
this problem on OS/390; although there have been many alternatives sent to
the list on this one.

On non-OS/390 DB2, you have the ROW_NUMBER function which performs well and
gives you what you need.

On OS/390, you could try DSNTEP2, as it automatically adds a line number to
the output (from memory). I'm not sure if you can do this in SPUFI or QMF.
Otherwise, you are stuck with writing a program etc...

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Clayton, Colin
Sent: Friday, January 11, 2002 6:56 AM
To: [login to unmask email]
Subject: Re: Getting Row number in 'SELECT'.


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








gilles godet

Re: Getting Row number in 'SELECT'.
(in response to Terry Purcell)

Deepak,
SELECT SUM(UN), B.FIRSTNME , B.LASTNAME
FROM (
SELECT FIRSTNME, LASTNAME ,1 AS UN FROM EMP
WHERE LASTNAME = 'SMITH'
) AS A ,
EMP B
WHERE NOT A.FIRSTNME!!A.LASTNAME > B.FIRSTNME!!B.LASTNAME
AND B.LASTNAME = 'SMITH'
GROUP BY B.FIRSTNME ,B.LASTNAME
Regards
Gilles godet
Deepak Kini M <[login to unmask email]> a écrit : 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


The Information contained and transmitted by this E-MAIL is proprietary to Wipro and/or its Customer and is intended
for use only by the individual or entity to which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this
E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent
of the intended recipient or a person responsible for delivering the information to the named recipient, you are
notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way
or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail &
notify us immediately at [login to unmask email]



---------------------------------
Yahoo! Courrier -- Une adresse @yahoo.fr gratuite et en français !

Mohammad Khan

Re: Getting Row number in 'SELECT'.
(in response to gilles godet)
Hi Deepak
You can do this ( V6 & later ) by an UDF. It's a strange coincidence that I
wrote this very UDF a little while ago as a simple demonstration for my
coworkers. Here is all the code :

CREATE FUNCTION PARISP.SEQ()
RETURNS INTEGER
EXTERNAL NAME UDF001C LANGUAGE COBOL
PARAMETER STYLE DB2SQL NOT DETERMINISTIC
NO SQL NO EXTERNAL ACTION
SCRATCHPAD 10 DISALLOW PARALLEL
WLM ENVIRONMENT WLMUNIT
PROGRAM TYPE MAIN ;

IDENTIFICATION DIVISION.
PROGRAM-ID. UDF001C.

DATA DIVISION.
WORKING-STORAGE SECTION.
LINKAGE SECTION.
*** RESULT
01 LK-RESULT PIC S9(9) COMP.
*** OUTPUT INDICATORS
01 LK-INDR PIC S9(4) COMP.

01 LK-SQLSTATE PIC X(5).
01 LK-PROC-NAME.
49 FILLER PIC S9(4) COMP.
49 FILLER PIC X(137).
01 LK-SPEC-NAME.
49 FILLER PIC S9(4) COMP.
49 FILLER PIC X(128).
01 LK-MESSAGE.
49 FILLER PIC S9(4) COMP.
49 FILLER PIC X(70).

01 LK-SCRATCHPAD.
05 FILLER PIC S9(9) COMP.
05 LK-COUNT PIC S9(9) COMP.
05 FILLER PIC X(6).

PROCEDURE DIVISION USING
LK-RESULT
LK-INDR
LK-SQLSTATE
LK-PROC-NAME
LK-SPEC-NAME
LK-MESSAGE
LK-SCRATCHPAD.

ADD 1 TO LK-COUNT.
MOVE LK-COUNT TO LK-RESULT.
MOVE ZERO TO LK-INDR.
MOVE '00000' TO LK-SQLSTATE.
GOBACK.

With this you can use SELECT SEQ(),... and you get what you want.
HTH
Mohammad


On Fri, 11 Jan 2002 14:15:08 +0500, Deepak Kini M <[login to unmask email]>
wrote:

>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
>



Suresh Sane

Re: Getting Row number in 'SELECT'.
(in response to Ruediger Kurtz)
Deepak,

As Ruediger points out, Steen's solution will not work. Clearly, what is is
needed is a ranking scheme. Assuming there are no duplicates (I don't mean
only 1 Smith but only 1 JOhn Smith), the solution is relatively simple. If
you allow for dups, you need to decide if both get the same # and if so,
which (lower or higger or halfway etc). This is definitely more complex.
Here is the simple solution that should work:

SELECT A.firstname, A.lastname, COUNT(*) AS SEQ_NO
FROM xxx A, xxx B
WHERE (A.lastname = B.lastname AND A.firstname <= B.firstname)
OR (A.lastname < B.lastname)
GROUP BY A.firstname, A.lastname
ORDER BY SEQ_NO

Have not tested it. Also, before I get a flood of emails - performance will
be terrible!!! for any decent sized table, so beware.

HTH.

Suresh

>From: Ruediger Kurtz <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Getting Row number in 'SELECT'.
>Date: Fri, 11 Jan 2002 14:12:13 +0100
>
>Hmmmm, doesn't this give
>
>adam smith 1
>benny smith 1
>clark smith 1
>....
>
>Regards Ruediger Kurtz
>
> > "Rasmussen, Steen" schrieb:
> >
> > 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
>
>--
> >"""""<
> !_ _!
> (o) (o)
>oOOO--(_)--OOOo------------------------------------------------------*
>* *
>* Rüdiger Kurtz *
>* *
>* HUK-Coburg Phone: 09561/96-3914 *
>* Versicherungen-Bausparen Fax : 09561/96-3678 *
>* Abt. Informatik - Betrieb (Datenbank-Administration) *
>* Bahnhofsplatz Mail: [login to unmask email] *
>* 96450 Coburg *
>* *
>* .oooO *
>*--( )--Oooo.------------------------------------------------------*
> \ ( ( )
> \_) ) /
> (_/
>
>
>
>
>


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.



Gary V (ING) Scarcella

Re: Getting Row number in 'SELECT'.
(in response to Suresh Sane)
In QMF you can use the &ROW variable to provide this.

Also, here's an example of the ROW_NUMBER() function solution for non-OS/390
DB2 systems.

connect to gvstest1;
-- select rows and provide row# for each result set row
select row_number() over() as row#
, q.*
from q.staff q
where dept <= 20
order by dept, name;
connect reset;

ROW# ID NAME DEPT JOB YEARS SALARY COMM

-------------------- ------ --------- ------ ----- ------ ---------
---------
1 240 DANIELS 10 MGR 5 19260.25
-
2 260 JONES 10 MGR 12 21234.00
-
3 210 LU 10 MGR 10 20010.00
-
4 160 MOLINARE 10 MGR 7 22959.20
-
5 50 HANES 15 MGR 10 20659.80
-
6 170 KERMISCH 15 CLERK 4 12258.50
110.10
7 110 NGAN 15 CLERK 5 12518.20
206.60
8 70 ROTHMAN 15 SALES 7 16502.83
1152.00
9 80 JAMES 20 CLERK - 17666.69
128.20
10 20 PERNAL 20 SALES 8 23771.67
612.45
11 10 SANDERS 20 MGR 7 24015.29
-
12 190 SNEIDER 20 CLERK 8 18645.40
126.50



Gary Scarcella
ING-AFS/IT/IM/DBA - TSB2
Email: mailto:[login to unmask email] <mailto:[login to unmask email]>


-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
<mailto:[mailto:[login to unmask email]>
Sent: Friday, January 11, 2002 9:29 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: Re: Getting Row number in 'SELECT'.

Colin,

SELECT COUNT(expression) is a feature of V7, and supplements COUNT(*) &
COUNT(DISTINCT expression).
COUNT(*) - counts all qualifying rows
COUNT(DISTINCT expression) - counts unique occurrences of "expression" with
duplicates eliminated.
COUNT(expression) - counts all occurrences of "expression" with duplicates
eliminated.
Damien,
As David stated, there aren't any good performing "SQL only" solutions to
this problem on OS/390; although there have been many alternatives sent to
the list on this one.
On non-OS/390 DB2, you have the ROW_NUMBER function which performs well and
gives you what you need.
On OS/390, you could try DSNTEP2, as it automatically adds a line number to
the output (from memory). I'm not sure if you can do this in SPUFI or QMF.
Otherwise, you are stuck with writing a program etc...
Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com < http://www.ylassoc.com >

.