I have a table like this

create table a_table
(
    id         int unique,
    filter_val text,
    sort_val   text
);

insert into a_table(id, filter_val, sort_val)
values (1, 'a3', 'a2'),
       (2, 'a4', 'a3'),
       (3, 'a5', 'a4'),
       (4, 'a3', 'a1'),
       (5, 'a2', 'a3');

How can I select a top N unique values filter_val, but ordered by sort_val? As example, if I select top 3 unique value it equals 'a3','a4','a2'. Top 4 values is 'a3','a'4','a2','a5'.

I`m trying to use select distinct filter_val, but it forbid a order by sort_val.

CodePudding user response：

This may aswer partially your question, this is how you can sort by sort_val:

select min(id) id, filter_val, min(sort_val) sort_val
from a_table
group by filter_val
order by min(sort_val) 

id|filter_val|sort_val|
-- ---------- -------- 
 1|         1|       1|
 2|         2|       3|
 5|         3|       3|
 3|         4|       4|

and with different sorting order:

select min(id) id, filter_val, min(sort_val) sort_val
from a_table
group by filter_val
order by min(sort_val) desc

id|filter_val|sort_val|
-- ---------- -------- 
 3|         4|       4|
 2|         2|       3|
 5|         3|       3|
 1|         1|       1|

another way:

select max(id) id, filter_val, max(sort_val) sort_val
from a_table
group by filter_val
order by max(sort_val) desc

id|filter_val|sort_val|
-- ---------- -------- 
 3|         4|       4|
 2|         2|       3|
 5|         3|       3|
 4|         1|       2|

CodePudding user response：

PostgreSQL also has DISTINCT ON

select *
from
(
  select distinct on (filter_val) *
  from a_table
  order by filter_val asc, sort_val
) q
order by sort_val
limit 4;

db<>fiddle here