Selecting specific rows from a table, using a group by query

Question

I have a table which looks something like this:

+------------+------------+--------------+
| Date       | Name       | Certificates |
+------------+------------+--------------+
| 2021-02-01 | Jason      | 3            |
| 2021-02-01 | Nisha      | 4            |
| 2021-02-01 | Zaid       | 5            |
| 2021-03-25 | Aniket     | 4            |
| 2021-03-25 | Anish      | 2            |
| 2021-03-25 | Nadia      | 0            |
| 2021-05-06 | Aadil      | 7            |
| 2021-05-06 | Ashish     | 1            |
| 2021-05-06 | Rahil      | 9            |
+------------+------------+--------------+

This result is obtained by performing the following SQL query:

SELECT 
    Date, Name, COUNT(Certificates) as Certificates
FROM Students.data
GROUP BY Date, Name
ORDER BY Date, Name;

After receiving this result, ideally, I would like only the first entry from each date now (that would basically be the first name for each date), which should be something like this:

+------------+------------+--------------+
| Date       | Name       | Certificates |
+------------+------------+--------------+
| 2021-02-01 | Jason      | 3            |
| 2021-03-25 | Aniket     | 4            |
| 2021-05-06 | Aadil      | 7            |
+------------+------------+--------------+

Is there a way I can modify the above group by query to obtain the result, or do I need to pass the result of this query to some other query, if so, what would that query be. Thanks.

Also, the database I am using is Clickhouse.

NOTE: Please let me know if there is any issue with the question, can clarify that.

Answer 1

You consider your result an intermediate result of which you want to pick one row per date. You can use ROW_NUMBER for this to number the rows per date by name and only keep a date's first row (those rows numbered 1).

SELECT date, name, certificates
FROM
(
  SELECT 
    date, name, COUNT(Certificates) AS certificates,
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY name) AS rn
  FROM students.data
  GROUP BY date, name
) numbered
WHERE rn = 1
ORDER BY date;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=93c3682bda72cb4fe53fbbe8053a8acb (using MySQL 8 here, because dbfiddle.uk doesn't feature clickhouse, but the query is standard SQL compliant, so we can use about every modern RDBMS for the demonstration).

Answer 2

While looking at your output, I assumed that you wanted the sole entry for the day to be the one with alphabetically ASC on Name column.

In such case, you can use ROW_NUMBER() function if this SQL server

SELECT Date,Name, Certificates
FROM
(
SELECT 
    Date, Name, 
    Certificates=COUNT(Certificates) OVER (PARTITION BY Date,Name) 
    RowNumber = ROW_NUMBER() OVER (PARTITION BY Date
     ORDER BY Name ASC) 
FROM Students.data
) T 
WHERE RowNumber =1 
ORDER BY Date ASC
;

Answer 3

• use CTE instead of Subquery
• rank the data => each row will have same data with an increasing rank --> ROW_NUMBER
• filter the rank_ by by 1 to get one entry per date
• the order by is on name in alphabetical order assuming that what you need

If you don't have the count already then use code 1 fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a5a8bf3f6934f18b19d331d3ba43570a

WITH ranked_data AS ( SELECT date_, name, count(certificates_) over(partition by date_,name) as certificates, row_number() OVER(PARTITION BY date_ order by name) as rank_ FROM students ) SELECT date_, name, certificates FROM ranked_data WHERE rank_ = 1

if you have the count then use code 2

WITH ranked_data AS (
SELECT date_, name, certificates_,
row_number() OVER(PARTITION BY date_ order by name) as rank_
FROM students
)
SELECT 
  date_, name, certificates_ FROM ranked_data WHERE rank_ = 1

Answer 4

• straightforward way

SELECT Date, untuple(groupArray(tuple(Name, Certificates))[1])
FROM (
    SELECT *
    FROM  (
        /* Emulate the test dataset. */
        SELECT toDate(data.1) AS Date, data.2 AS Name, data.3 AS Certificates
        FROM (
            SELECT arrayJoin([
                ('2021-02-01', 'Jason ', 3),
                ('2021-02-01', 'Nisha ', 4),
                ('2021-02-01', 'Zaid  ', 5),
                ('2021-03-25', 'Aniket', 4),
                ('2021-03-25', 'Anish ', 2),
                ('2021-03-25', 'Nadia ', 0),
                ('2021-05-06', 'Aadil ', 7),
                ('2021-05-06', 'Ashish', 1),
                ('2021-05-06', 'Rahil ', 9)]) AS data
            )
        )
    ORDER BY Date, Name
    )
GROUP BY Date

/*
┌───────Date─┬─Name───┬─Certificates─┐
│ 2021-02-01 │ Jason  │            3 │
│ 2021-03-25 │ Aniket │            4 │
│ 2021-05-06 │ Aadil  │            7 │
└────────────┴────────┴──────────────┘
*/

• way based on window-function

Starting from version 21.4 added the full support of window-functions. At this moment it was marked as an experimental feature.

SELECT DISTINCT
    Date,
    FIRST_VALUE(Name) OVER w AS FirstName,
    FIRST_VALUE(Certificates) OVER w AS FirstCertificates
FROM 
(
    /* Emulate the test dataset. */
    SELECT toDate(data.1) AS Date, data.2 AS Name, data.3 AS Certificates
    FROM (
        SELECT arrayJoin([
            ('2021-02-01', 'Jason ', 3),
            ('2021-02-01', 'Nisha ', 4),
            ('2021-02-01', 'Zaid  ', 5),
            ('2021-03-25', 'Aniket', 4),
            ('2021-03-25', 'Anish ', 2),
            ('2021-03-25', 'Nadia ', 0),
            ('2021-05-06', 'Aadil ', 7),
            ('2021-05-06', 'Ashish', 1),
            ('2021-05-06', 'Rahil ', 9)]) AS data
        )
)
WINDOW w AS (PARTITION BY Date ORDER BY Name ASC)
SETTINGS allow_experimental_window_functions = 1

/*
┌───────Date─┬─FirstName─┬─FirstCertificates─┐
│ 2021-02-01 │ Jason     │                 3 │
│ 2021-03-25 │ Aniket    │                 4 │
│ 2021-05-06 │ Aadil     │                 7 │
└────────────┴───────────┴───────────────────┘
*/

See https://altinity.com/blog/clickhouse-window-functions-current-state-of-the-art.