SQL. How to obtain unique values from table but only for 1 column?-CodePudding

I have a table1 . I sort this table by name, and by time column :

SELECT name, value 
FROM table1
WHERE time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00'  
ORDER BY name, time

AS result i got next table :

    name,   value,  time
    A,      5,      2022-05-23 01:01:12
    A,      9,      2022-05-23 01:02:11
    A,      7,      2022-05-23 01:03:21
    B,      5,      2022-05-23 01:04:23
    B,      6,      2022-05-23 01:05:33
    C,      7,      2022-05-23 01:06:30
    C,      8,      2022-05-23 01:07:41
    C,      3,      2022-05-23 01:08:44
    C,      7,      2022-05-23 01:09:50

Then i need select all unique names with lowest time, and got values for this names:

Result should be like this:

name,   value,  time
A,      5,      2022-05-23 01:01:12
B,      5,      2022-05-23 01:04:23
C,      7,      2022-05-23 01:06:30

=====================================================================

It turns out that we just took each first unique name from the table:

name,   value,  time
A,      5,      2022-05-23 01:01:12 <- take this one
A,      9,      2022-05-23 01:02:11
A,      7,      2022-05-23 01:03:21 
B,      5,      2022-05-23 01:04:23 <- take this one
B,      6,      2022-05-23 01:05:33
C,      7,      2022-05-23 01:06:30 <- take this one
C,      8,      2022-05-23 01:07:41
C,      3,      2022-05-23 01:08:44
C,      7,      2022-05-23 01:09:50

My goal is take unique names with lowest timestamp (or each first unique name from table, because it's already sorted by timestamp)

I'm not clear how to get wanted result. I tried use "SELECT DISTINCT name,values" but it's back all unique names AND unique values, but i'm need got ONLY unique names values (where time is lowest)

CodePudding user response：

Generally you would use ROW_NUMBER, but Clickhouse does not support analytic functions. We can use a join approach instead:

SELECT t1.*
FROM table1 t1
INNER JOIN
(
    SELECT name, MIN(time) AS min_time
    FROM table1
    WHERE time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00'
    GROUP BY name
) t2
    ON t2.name = t1.name AND
       t2.min_time = t1.time
WHERE
    time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00';