How to select rows with only one unique SQLite column?

Question

SQL

Hello, there is a DB with 4 columns ( id (INTEGER PRIMARY KEY), security_id, date_and_time, price) How do I select rows (exactly all its values) in which only the price column is unique?

Answer 1

SELECT X.ID, X.SECURITY_ID, X.DATE_AND_TIME, X.PRICE FROM (
SELECT CASE WHEN NEXT_VALUE = 0 THEN 1
WHEN NEXT_VALUE = PRICE THEN 0
ELSE 1 END AS CHECK_VALUE, A.* FROM (
SELECT A.*,LEAD( A.PRICE, 1, 0) OVER(ORDER BY ID) NEXT_VALUE FROM
TAB A ) A ) X WHERE CHECK_VALUE = 1 ORDER BY ID

Answer 2

You can get rid of duplicates by adding the DISTINCT keyword to your query. It is specified in the SELECT clause, immediately after the SELECT keyword:

SELECT DISTINCT <Перечень столбцов>
FROM <Перечень таблиц>

Unique records - SELECT DISTINCT

Answer 3

For sqllite you can try this.

SELECT
  MIN(id) ,
  price
FROM
  your_table
GROUP BY
  price
HAVING COUNT(price) = 1;

The query should return row IDs that have a unique price.