CODEWITHSUNDEEP
sqlpostgresql
mrbox
mrbox

Reputation: 824

Get sequential number of related object

I was completely not sure how to compose title so sorry for not very descriptive topic.

I have two tables, A and B. B.foo is a FK to A.id. Table B also has an id column.

Is there a way to get result like:

|a_id|b_id|b_foo_number
|1   |1   |1
|1   |2   |2
|1   |56  |3
|2   |3   |1
|2   |12  |2
|2   |57  |3

From table B looking like this:

|id|foo
|1 |1
|2 |1
|56|1
|3 |2
|12|2
|57|2

Right now I'm completely clueless, how could I do it.

Upvotes: 0

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

The following produces the output you specify:

select 1 as a_id, b_id,
       row_number() over (partition by foo order by a_id) as b_foo_number
from b;

However, it is not clear that this is what you really need. You need to better specify your question.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Use ROW_NUMBER:

SELECT foo AS a_id, id AS b_id, 
       ROW_NUMBER() OVER (ORDER BY id) AS b_foo_number
FROM B
WHERE foo = 1

If you want to get sequential numbers per foo slices, then use PARTITION BY:

SELECT foo AS a_id, id AS b_id, 
       ROW_NUMBER() OVER (PARTITION BY foo ORDER BY id) AS b_foo_number
FROM B
ORDER BY foo

Upvotes: 2

Related Questions