How do you track and count the rows output? 3

[ljsmith91]

Sorry, I hardly ever use SQL but I have a query I need to modify and create a new field that has a counter for every row output from the query. How do you establish a counter that increments for each row output? This is probably SQL 101 but like I say, just a rookie. Thanks in advance.

 

[SantaMufasa]

This is probably SQL 101 but like I say, just a rookie.

First, LJ, we were all rookies once. And until someone shows us how to do something, how could we be expected to come up with a solution, using a particular feature, out of thin air? <grin>. You never should be embarrassed about asking a question. I'm glad you asked this one.


A helpful feature to show "a counter for every row output from the query" is Oracle's ROWNUM function. Let us know if the use of ROWNUM in the following query resolves your need:

SELECT ROWNUM,LAST_NAME,SALARY FROM S_EMP;

ROWNUM LAST_NAME                     SALARY
------ ------------------------- ----------
     1 Velasquez                       2500
     2 Ngao                            1450
     3 Nagayama                        1400
     4 Quick-To-See                    1450
     5 Ropeburn                        1550
     6 Urguhart                        1200
     7 Menchu                          1250
     8 Biri                            1100
     9 Catchpole                       1300
    10 Havel                           1307
    11 Magee                           1400

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ljsmith91]

Mufas,

Thanks so much. It did he trick. However, in the query the "ORDER BY field" is off another field. The ROWNUM then gets unsorted in the output. If I change it to the "ORDER BY ROWNUM,Field" then my field which I need sorted is now unsorted. Any way this can be addressed so that I get my field sorted and the rownum also sorted.

Thanks again for pointing me in the right direction.

lj

 

[Dagon]

SELECT ROWNUM,LAST_NAME,SALARY FROM
(SELECT last_name, salary FROM S_EMP ORDER BY salary);

For Oracle-related work, contact me through Linked-In.

 

[Turkbear]

Hi Dagon,
back in the day when I needed it,that was the best tip I ever learned about counting and ordering in Oracle--Thanks for refeshing the knowledge base with it..





To Paraphrase:"The Help you get is proportional to the Help you give.."