I need a script to create a table . The problem is my select for creating the table contains equal values in some rows. I need to use distinct for these columns. The other columns' values can come from any matching row.
My current result table has data like this:
| CITY | STREET | STREET_NUM | VAL_X | VAL_Y |
------------------------------------------------------------------
| CityA | Street abc | 5 | 11.5 | 0.5 |
| CityA | Street abc | 5 | 15.4 | 1.8 |
| CityA | Street abc | 5 | 12.4 | 2.8 |
| CityB | Street xyz | 18 | 5.4 | 1.9 |
| CityB | Street xyz | 18 | 8.4 | 1.1 |
| CityC | Street klm | 55 | 9.6 | 0.8 |
But I need data like this:
| CITY | STREET | STREET_NUM | VAL_X | VAL_Y |
------------------------------------------------------------------
| CityA | Street abc | 5 | 11.5 | 0.5 |
| CityB | Street xyz | 18 | 5.4 | 1.9 |
| CityC | Street klm | 55 | 9.6 | 0.8 |
For columns city, street and street_num I need to apply distinct. val_x and val_y should be used anyone, for example first of that group with same city, street and street_num.
Can you give me advice how to edit this script?
my_result
table(refer to the fiddle) then it might work as you expected. And yes, my sample data was different. The values in my example were rounded up. Here is the fiddle.city, street and street_num I need to apply distinct
. Is that supposed to beDISTINCT ON (city, street, street_num)
orDISTINCT ON (city)
andDISTINCT ON (street)
andDISTINCT ON (street_num)
- in this case you need to define priorities or you get arbitrary results. Very different cases. 2. Doval_x
andval_y
have to come from the same (even if arbitrary) row? 3. We need table definitions showing data types and constraints and your version of Postgres to give the best answer.