How do I return a sequential record count ? 3

[TopJack]

I hope this is the right forum for this type of question.

Is it possble to return an incrementing record count with SQL. I'm using Crystal Reports 9 sql editor against an Oracle database.

For example I have a simple SQL query like this ......

SELECT employee_name FROM employees

which returns this .......

[blue]
dave
peter
sandra
rebecca
wayne
[/blue]

The question is, how would I get an output like this ........

[green]
1 dave
2 peter
3 sandra
4 rebecca
5 wayne
[/green]

I think there is a field called "rownum" in Oracle SQL but not sure if I can use.

Any ideas or suggestions would be appreciated.

Thanks.

 

[TopJack]

Sorry guys I've managed to answer my own question.

I can use the "ROWNUM" field to return sequential record counting.

Example as before ......

SELECT ROWNUM, employee_name FROM employees

Job done. Thanks anyway.

 

[r937]

only in oracle, though, not in ANSI SQL

r937.com | rudy.ca

 

[TopJack]

How would I do the equivalent in ANSI SQL ?

 

[PHV]

A starting point:
SELECT Count(*) AS Rank, A.employee_name
FROM employees AS A INNER JOIN employees AS B ON A.employee_name >= B.employee_name
GROUP BY A.employee_name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

i would do this in the application layer that displays the query results, using a simple counter if the language doesn't already have a property for this (like coldfusion's queryname.currentRow)

as PHV has shown, it is possible to do this with sql, but it's probably not a good idea, especially as the number of rows increases...



r937.com | rudy.ca

 

[r937]

i'm sure there is also some new RANK OVER syntax (which i haven't learned yet) in one of the more recent versions of ANSI SQL

r937.com | rudy.ca

 

[Golom]

PHV's code will produce duplicate numbers if there are duplicate values in the "employee_name" field. That's not a knock against PHV ... it's just the nature of the beast.

 

[TopJack]

Thanks PHV for the code.

Golom/PHV: How would I compensate for duplicates in the results ?

 

[PHV]

For distinct rank values, the group by clause should evaluate UNIQUE values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

what PHV means is that if you have two employees with the name Todd then you can either get a duplicated rank number (both Todds will have the same rank, and there should be a gap in the numbers after them) like this --

1 dave
2 peter
3 rebecca
4 sandra
5 todd
5 todd
7 wayne

or else you can rank the names instead of the employees (and thus lose the information that you really have two Todds) like this --

1 dave
2 peter
3 rebecca
4 sandra
5 todd
6 wayne






r937.com | rudy.ca

 

[PHV]

Say your table has a PK on a field named ID:
SELECT Count(*) AS Rank, A.employee_name
FROM employees AS A INNER JOIN employees AS B ON A.employee_name || ',' || A.ID >= B.employee_name || ',' || B.ID
GROUP BY A.employee_name, A.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TopJack]

Thanks ever so much guys. You really know your stuff.

I might be going of subject here but can I ask a silly question.

My original question was around the Crystal Reports SQL engine for an Oracle DB. The ROWNUM field has been useful but does have limitations as r937 pointed out. I love your code PHV. I know my source data has a primary key with no duplicates on the employee name (the field has a name and unique employee id concatenated for all records). I'm just starting out learning SQL syntax but I tried PHV code through a MS Access setup to have a play. This was the code I ran using r937 data.

SELECT Count(*) AS Rank, A.employee_name
FROM employees AS A INNER JOIN employees AS B ON A.employee_name>=B.employee_name
GROUP BY A.employee_name;

The results were as follows :-

[blue]
1 dave
2 peter
3 rebecca
4 sandra
12 todd
7 wayne
[/blue]

Just to improve my understanding because you have answered my question already, why has "todd" shown a 12 and "wayne" a 7 ?

 

[TopJack]

Further to my previous post I guess this is what happens :-

[green]
1 dave
2 peter
3 rebecca
4 sandra
6 todd
6 todd
7 wayne
[/green]

Why is the SQL summing up todd's count to 12 ?

 

[PHV]

You have 2 times 6 rows matching the JOIN ...
You may try this:

SELECT Count(*) AS Rank, A.employee_name
FROM (SELECT DISTINCT employee_name FROM employees) AS A
INNER JOIN employees AS B ON A.employee_name >= B.employee_name
GROUP BY A.employee_name

Results:[tt]
Rank employee_name
1 dave
2 peter
3 rebecca
4 sandra
6 todd
7 wayne[/tt]

And with an unique ID field (JetSQL syntax):

SELECT Count(*) AS Rank, A.employee_name
FROM employees AS A INNER JOIN employees AS B ON A.employee_name & ',' & A.ID >= B.employee_name & ',' & B.ID
GROUP BY A.employee_name, A.ID

[tt]Rank employee_name
1 dave
2 peter
3 rebecca
4 sandra
5 todd
6 todd
7 wayne[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dnoeth]

Hi Rudy,
SQL:1999/SQL:2003 implements 3 different ranking functions:

RANK() OVER (ORDER BY employee_name):
(number of rows where employee_name < current employee_name) + 1
-> there migth be gaps:

Rank employee_name
1 dave
2 peter
3 rebecca
4 sandra
5 todd
5 todd
7 wayne


DENSE_RANK() OVER (ORDER BY employee_name):
number of distinct values where employee_name <= current employee_name
-> no gaps:

Rank employee_name
1 dave
2 peter
3 rebecca
4 sandra
5 todd
5 todd
6 wayne


ROW_NUMBER() OVER (ORDER BY employee_name):
sequential row number
-> no gaps, unique numbers:

Rank employee_name
1 dave
2 peter
3 rebecca
4 sandra
5 todd
6 todd
7 wayne


In addition there'a PARTITION BY option which is similar to a GROUP BY, it restarts the calculation within each group.



And if you buy those three, you get two distribution functions for free

PERCENT_RANK() OVER (ORDER BY employee_name):
relative rank results in a value >= 0 and <= 1,
(rank / number of rows in partition)

Rank employee_name
0.00 dave
0.?? peter
0.?? rebecca
0.?? sandra
0.?? todd
0.?? todd
1.00 wayne


CUME_DIST results in a value > 0 and <= 1, but i don't know you the exact definfition by heart...


And as an extra there are Cumulative/Group/Moving aggregates and more ...


Dieter

 

[r937]

thanks dieter

r937.com | rudy.ca

 

[TopJack]

Thanks to all, I've learnt a lot.

 

[ChillAPAC]

I've found this for Oracle SQL, not sure about ANSI SQL:

count(employee_name)over (
ORDER BY
100 asc rows unbounded preceding) row_count