add a "Sequence" value in a VIEW

if this were a simple table, than a AutoInc PK could be used, but its a view formed by unions and joins from a number of tables, but I need each resulting record to have a sequential ID number

CREATE VIEW myVIEW AS SELECT DISTINCT f1,f2,f3, [SOME WAY TO NUMBER THE RECORD]
FROM (
(<select stuff>) UNION (select stuff) UNION (<select more stuff)

and some of those have LEFT JOINS in them

Records will be selected based on F1, F2 OR F3, and the ID number is required.

Look at the ROW_NUMBER function.

:slight_smile: I thought of that about 10 seconds after I hit [POST]

I cannot figure out how to make it work

my query works until I add

,ROW_NUMBER () OVER (ORDER BY mother,father,child) family

Error near “(” [I LOVE SQL errors!]

ALso doesn’t work <3.25 version and I have tools useing 3.14 still

and I just realized it is NOT a sequentail number for each… but for “groups”

The fields I have are F1, F2, F3 where I need to number the unique PAIRS of F2/F3

If all else fails, create a temporary in-memory table
Insert records from your query
Then query the table and take the row_num from that

Can’t you create a trigger based on the view to update the field as a concat of the required fieldsc?