I would like to process the following table that has multiple primary keys.
key_a and key_b are the primary keys.
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)
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.
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 alias
min (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.
- sqlite - i want to retrieve data using the count result as a condition in sql
- the prediction result of fx chart by lstm of rnn using keras in python deviates from the actual data
- create conditional expression using sql subquery
- php - i want to narrow down by using the result of calculating the column value using laravel where
- result difference between sql aggregation function and arithmetic operation
- sql server - i want to obtain the result by excluding a specific value from the duplicate values in sql
- python - i want to make the execution result successful by using to_excel method
- i don't understand the meaning of using ":" in sql
- i want to get the result of submit using php goutte
- sql server - how to display data using inline views
- i want to adjust the execution result using the while statement in python as expected
- bash - [sh] i want to process another file by using the result of matching performed on one file
- i want to create an er diagram from a sql file using mysql workbench
- sql server - download forms using ssrs from the aspnet (c #) screen
- how to display data in multiple tables using sql with clause
- sql - data update using update and join
- php - using preg_match with the same character does not result in a partial match
- the result of the program using the structure is all 0
- python - how to retrieve result using recursive algorithm
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- i want to call a child component method from a parent in vuejs
- python 3x - typeerror: 'method' object is not subscriptable
- dart - flutter: the instance member'stars' can't be accessed in an initializer error
- xcode - pod install [!] no `podfile 'found in the project directory