For a given table give out the count for each row Oracle SQL

Question

if i have a table like this:

ID  | Name   | Payment
----------------------
101 | Victor | 10
103 | Andy   | 13
134 | Mai    | 2
156 | Chris  | 68
179 | Ryan   | 43

And I wanna have a query that gives out the following

[Count] | ID  | Name   | Payment
----------------------
1       | 101 | Victor | 10
2       | 103 | Andy   | 13
3       | 134 | Mai    | 2
4       | 156 | Chris  | 68
5       | 179 | Ryan   | 43

So it gives out the number of each row but I don't know how to do it ( am a beginner at SQL). Any tips?

Gordon Linoff · Accepted Answer · 2019-03-19 16:33:27Z

2

Simply use row_number():

select row_number() over (order by id) as "Count",
       t.*
from t
order by "Count";

You should have both order bys to be sure the numbering on the rows is correct and the order of the rows in the result set is correct.

answered Mar 19, 2019 at 16:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-19 16:39:21Z

0

use dense_rank()

select *,dense_rank() over(order by id) from t

or you could use count()

  select *,count(*) over(order by id) from t

edited Mar 19, 2019 at 16:39

answered Mar 19, 2019 at 16:33

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

Hijesh V L · Accepted Answer · 2019-03-19 17:02:24Z

0

simple

select rownum, ID , Name, Payment from table

answered Mar 19, 2019 at 17:02

Hijesh V L

2792 silver badges11 bronze badges

Collectives™ on Stack Overflow

For a given table give out the count for each row Oracle SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related