SQL aggregate data based on a single column-CodePudding

i have to aggregate data in my SQL query (DISTINCT) based on a single column in this kind of fashion:

original table:

id|var_val|id_res_id|var_id_id|device_id|var_val_conv
11|126    |3864     |36       |abc123   |1.21
10|136    |3764     |36       |abc123   |1.51
9 |115    |3582     |35       |abc123   |4.41
8 |143    |2464     |36       |abc123   |8.81
7 |226    |1844     |35       |abc123   |9.21
6 |12     |3364     |34       |abc123   |11.7

so i would to aggregate data based on column var_id_id having just first data fo every different values of var_id_id as in example:

result:

id|var_val|id_res_id|var_id_id|device_id|var_val_conv
11|126    |3864     |36       |abc123   |1.21
9 |115    |3582     |35       |abc123   |4.41
6 |12     |3364     |34       |abc123   |11.7

i try this query but i don't get what i would:

SELECT VR.id, var_val, id_res_id, var_id_id, AR.device_id, var_val_conv
    FROM public.api_site_varsresults as VR,
    public.modbus_modbusvariable as MV,
    public.api_site_results as AR
    WHERE VR.var_id_id = MV.id AND VR.id_res_id = AR.id
    AND AR.device_id = 'abc123'
    GROUP BY VR.id, var_id_id, AR.device_id
    ORDER BY VR.id DESC;

How can i group for var_id_id having just first line encountred for every result?

So many thanks in advance

CodePudding user response：

In this case you can use window function first_value in next way:

WITH ids AS (SELECT DISTINCT
    first_value(id) over (partition by var_id_id order by id desc) id
FROM api_site_results as VR
) SELECT * FROM ids JOIN api_site_results USING(id);

PostgreSQL window functions online

Result:

 ==== ========= =========== =========== =========== ============== 
| id | var_val | id_res_id | var_id_id | device_id | var_val_conv |
 ==== ========= =========== =========== =========== ============== 
| 11 | 126     | 3864      | 36        | abc123    | 1.21         |
 ---- --------- ----------- ----------- ----------- -------------- 
| 9  | 115     | 3582      | 35        | abc123    | 4.41         |
 ---- --------- ----------- ----------- ----------- -------------- 
| 6  | 12      | 3364      | 34        | abc123    | 11.7         |
 ---- --------- ----------- ----------- ----------- --------------

CodePudding user response：

The "distinct on" from postresql will do that

select distinct on(var_id_id) t.*
  from api_site_results t 
  order by var_id_id;

dbfiddle