Is there way to add a field in a parent query that will increment as the query goes through all values generated in a subquery?

Question

I think I have a table that lacks a true primary key and I need to make one in the output. I cannot modify the table.

I need to run a select query to generate a list of values (list_A), then take those values and query them to show all the records related to them. From those records, I do another select to extract a now visible list called list_B. From list_B, I can search them to reveal all the records related to the original list (list_A), with many of those records missing the values from list_A but still need to be counted.

Here's my process so far:

I declared a sequence called 'temp_key', which starts from 1 and increments by 1.
I add a field called 'temp_key' to the parent query, so that it will hopefully show which element of the original list_A sub-query the resulting records are related to.
I run into trouble because I don't know how to make the temp_key increment as the list_A sub-query moves from the beginning to end of all the values in the list.

SELECT currval(temp_key) AS temp_key, list_A, list_B 
FROM table 
WHERE list_B IN (SELECT DISTINCT list_B 
                 FROM table 
                 WHERE list_A IN (SELECT DISTINCT list_A 
                                  from table);

As it is now, the above query doesn't work because there seems to be no way to make the current value of temp_key increment upward as it goes through values from the list originally generated from the lowest level sub-query (list_A).

For example, there might be only 10 values in list_A. And the output could have 100s of records, all labeled 1 through 10, with many of those values missing values in the list_A field. But they still need to be labeled 1 through 10 because the values of list_B connect the two sets.

Sorry, I don't get it (and calling a _column_ a "list" doesn't really help either). Can you please **[edit]** your question (by clicking on the [edit] link below it) and add some sample data and the expected output based on that data as [formatted text](https://meta.stackoverflow.com/a/251362)? See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking text tables. (please [edit] your question - do **not** put code or additional information in comments) — , Feb 10 '21 at 06:50

score 0 · Answer 1 · answered Feb 10 '21 at 07:09

Maybe you can create a new primary key column first with the following code (concatenating row number with list_a):

WITH T AS (
        SELECT currval(temp_key) AS temp_key, list_A, list_B, 

        CONCAT(ROW_NUMBER() OVER(PARTITION BY list_A ORDER BY list_B),list_A) AS Prim_Key  
        FROM table )

SELECT * fROM T

Then you can specify in the where clause what keys you want to select

Is there way to add a field in a parent query that will increment as the query goes through all values generated in a subquery?

1 Answers1