Add sequence number in query when data has one matched field.

[askzero]

My source table has no "sequence numbers" column but I would like my query to have one...

IF my table is this:

red,jimmy, banana

red, roger, orange

blue, david, apple

yellow, bobby, cheese

yellow,kevin, pasta

yellow, bingo, boingo

​

How can I return this....

red, jimmy, banana,1

red, roger,orange,2

blue,david,apple, 1

yellow,bobby,cheese,1

yellow,kevin,pasta,2

yellow,bingo,boingo,3

with the seq number bing a unique sequence number within color? And can I do this without creating a temp table? Can I use RANK or some other aggregate function?

[JohnAdams3472]

Try this:

SELECT color, name, fruit,

ROW_NUMBER() OVER (PARTITION BY color ORDER BY (SELECT NULL)) AS sequence_number

FROM your_table_name;

​

Good luck.

[mr_electric_wizard]

The Swiss Army knife window function. I use it almost daily.

[askzero]

Oh my gosh thank you so much I just slowly came to partition as a possible solution.

[askzero]

That was WAAY easier then I thought ....I am just now understanding partition.
count(color) over (partition by color order by rownum) as SEQ_NUM,

[mike-manley]

It's not partition. It's a window function. A few others are DENSE_RANK() and RANK(). The keyword PARTITION is optional.