Home>

I would like to process the following table that has multiple primary keys.
key_a and key_b are the primary keys.

key_a key_b order_by
c b 1
a a 2
a b 3
b b Four

Exclude duplicate key_b for this table
And I want to get the result of key_b column in ascending order of order_by column.
(The result is as follows)

key_b order_by
b 1
a 2

But,

SELECT DISTINCT key_b FROM test_table


Then the order_by column should not be in ascending order

SELECT DISTINCT key_b FROM test_table ORDER BY order_by


Will result in an error

SELECT DISTINCT key_b, order_by FROM test_table ORDER BY order_by

Will not exclude duplicates.

I want to get is the value of key_b, and the result of the order_by column can be either.
DB is SQL and table is

create table public.test_table (
  key_a character varying (2) not null
  , key_b character varying (2) not null
  , order_by smallint not null
  , primary key (key_a, key_b)
);


It is created like this.

Please tell me how to describe it.

The SQL I'm using is PostgreSQL 11.

sql
  • Answer # 1

    select key_b, min (order_by) as seq
    from test_table
    group by key_b
    order by seq


    order byThe content specified by is an aliasseqNotmin (order_by)But yes

    select key_b
    from test_table
    group by key_b
    order by min (order_by)

  • Answer # 2

    If you don't use key_a and want to get the minimum order_by for the same key_b,

    ROW_NUMBER Analysis Function Usage Example ROW_NUMBER Function and PARTITION BY, ORDER BY Example
    Is it helpful?

    Even the same SQL cannot be used depending on the database and version, so it is better to specify it from the beginning.

  • Answer # 3

    It seems that you can go if you use having, but ...

    having count (key_b) = 1

    SQL to find duplicate data using GROUP BY and HAVING | Software Development Diary

    I'm sorry, I didn't understand the requirements correctly.