CODEWITHSUNDEEP
sqlsql-server
Michael Milewski
Michael Milewski

Reputation: 43

Ordering by rownumber in SQL

I would like to assing a ROW_NUMBER() in sql according to the number of rows by a particular other field. If I choose (order by that_field) then it just reorganizes the order of that field relative to the rownumber.

Example:

What I dont want

A1 \ 1...
A1 \ 2...
A1 \ 3...
A1 \ 4...
A2 \ 5...
A2 \ 6...
A2 \ 7...
A3 \ 8...

What I do want

A1 \ 1...
A1 \ 2...
A1 \ 3...
A1 \ 4...
A2 \ 1...
A2 \ 2...
A2 \ 3...
A3 \ 1...

Essentially, I want the rownumber to restart everytime the field changes value. Any help is greatly appreciated, thanks.

Upvotes: 2

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want partition by:

select row_number() over (partition by col1 order by col2)

Upvotes: 5

Related Questions