How to match and fill based on first record in SQL?

Currently have this table here:

i have two columns a URL which constantly changes and a url code – I want to take the first URL record and “fill” a new column where the URL_code matches – see outcome below

URL            | url_code 
---------------------------
gmail.com      | i_33
gob.com        | i_33
beans.com      | i_33
toast.com      | i_45
frog.com       | i_45
egg.com        | i_45

I would like to take the very first URL in the URL column for each url code, so i want to produce a table such as this:

URL            | url_code | taking_first_url
--------------------------------------------
gmail.com      | i_33     |  gmail.com  
gob.com        | i_33     |  gmail.com  
beans.com      | i_33     |  gmail.com  
toast.com      | i_45     |  toast.com
frog.com       | i_45     |  toast.com
egg.com        | i_45     |  toast.com

I have tried joining, case and when statements including LIKE in SQL but has not worked.

The url’s are different – I just want to take the first one

Any ideas?

Answer

For this to work, you need a column that can be used to order the records. I assume that such a column exists and is called id. In MySQL 8.0, you can use window function first_value():

select
    url,
    url_code,
    first_value(url) over(partition by url_code order by id) taking_first_url
from mytable

In earlier versions, you could use an inline query:

select
    t.url,
    t.url_code,
    (select url from mytable t1 where t1.url_code = t.url_code order by id limit 1) taking_first_url
from mytable t  

Demo on DB Fiddle:

| url       | url_code | taking_first_url |
| --------- | -------- | ---------------- |
| gmail.com | i_33     | gmail.com        |
| gob.com   | i_33     | gmail.com        |
| beans.com | i_33     | gmail.com        |
| toast.com | i_45     | toast.com        |
| frog.com  | i_45     | toast.com        |
| egg.com   | i_45     | toast.com        |