Onfly, function generated ID for Select Query

Hi,

Is it something like row_number() you want?

https://www.postgresql.org/docs/current/functions-window.html

KR
Mikael
________________________________
Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query

Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd

Dear Mikael!

Wooooow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY
product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER ()
OVER (*ORDER BY product_id, product_name, group_id*)
FROM products
*ORDER BY product_id, product_name, group_id*

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael <mikael.gustavsson@smhi.se> ezt írta (időpont: 2023.
márc. 10., P, 8:33):

No, the result will be ordered by the window functions order clause so no additional ordering is nessesary.
You can try this by changing it to OVER(ORDER BY product_name)

KR

Mikael

________________________________
Från: Durumdara <durumdara@gmail.com>
Skickat: den 10 mars 2023 08:41:06
Till: Gustavsson Mikael
Kopia: Postgres General
Ämne: Re: Onfly, function generated ID for Select Query

Dear Mikael!

Wooooow... that is it!
Thank you!

SELECT product_id, product_name, group_id, ROW_NUMBER () OVER (ORDER BY product_id) FROM products;

Does this mean that I have to duplicate the order by clause?

SELECT product_id, product_name, group_id, ROW_NUMBER ()
OVER (ORDER BY product_id, product_name, group_id)
FROM products
ORDER BY product_id, product_name, group_id

To get the same sequence in the ROW_ID-s, and the Query rows?

BR
dd

Gustavsson Mikael <mikael.gustavsson@smhi.se<mailto:mikael.gustavsson@smhi.se>> ezt írta (időpont: 2023. márc. 10., P, 8:33):

Hi,

Is it something like row_number() you want?

https://www.postgresql.org/docs/current/functions-window.html

KR
Mikael
________________________________
Från: Durumdara <durumdara@gmail.com<mailto:durumdara@gmail.com>>
Skickat: den 10 mars 2023 8:12
Till: Postgres General
Ämne: Onfly, function generated ID for Select Query

Dear Members!

I'm searching for a simple solution, like this:

select GetIDForThisSelect(1, 1) as UID, * from anytablehasdoublestringkey
join othertablehasnotuniqueintegerkey ...
where ....

Ok, I can make a sequence, but I don't want to use persistent data.
It is enough to get 1...N as UID for this select.

Like generate_series, but that is generating rows.

Maybe you have some trick to do this.

Thank you for any assistance!

Best regards
dd