I'm using SQLite. I want to get every data rows in 2 columns are unique.

This example data in order_transaction table:

When I use the distinct query:

SELECT DISTINCT date_transaction, customer FROM order_transaction;

The output is:

Ok so I get customer unique row, but how can I get the date_transaction unique row like this?

What query can get that output?

I'm new in SQLite. Sorry :)

CodePudding user response：

With a CASE expression and ROW_NUMBER() window function:

SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY t.date_transaction ORDER BY t.customer) = 1 THEN t.date_transaction END date_transaction, 
       t.customer
FROM order_transaction t
GROUP BY t.date_transaction, t.customer
ORDER BY t.date_transaction, ROW_NUMBER() OVER (PARTITION BY t.date_transaction ORDER BY t.customer);

See the demo.