CODEWITHSUNDEEP
mysqlsqlleft-join
oussaka
oussaka

Reputation: 960

mysql join with min value inside group by

I am attempting to write a query.

My table is:

+----+---------+------------+
| id | user_id |    date    |
+----+---------+------------+
|  1 |       1 | 2013-04-01 |
|  2 |       1 | 2017-01-01 |
|  3 |       1 | 2018-07-01 |
|  4 |       2 | 2018-09-01 |
|  5 |       2 | 2018-05-01 |
|  6 |       3 | 2018-10-01 |
|  7 |       1 | 2012-01-01 |
|  8 |       3 | 2016-06-01 |
|  9 |       3 | 2011-01-01 |
| 10 |       1 | 2000-01-01 |
+----+---------+------------+

Expected Result:

+----+---------+------------+-------------+
| id | user_id |    date    | first_login |
+----+---------+------------+-------------+
|  1 |       1 | 2013-04-01 | 2000-01-01  |
|  2 |       1 | 2017-01-01 | 2000-01-01  |
|  3 |       1 | 2018-07-01 | 2000-01-01  |
|  4 |       2 | 2018-09-01 | 2018-05-01  |
|  5 |       2 | 2018-05-01 | 2018-05-01  |
|  6 |       3 | 2018-10-01 | 2011-01-01  |
|  7 |       1 | 2012-01-01 | 2000-01-01  |
|  8 |       3 | 2016-06-01 | 2011-01-01  |
|  9 |       3 | 2011-01-01 | 2011-01-01  |
| 10 |       1 | 2000-01-01 | 2000-01-01  |
+----+---------+------------+-------------+

Is it possible ? it seems to me that it's a combination of joins !

I tried that, but it does not work. the execution is endless.

CREATE TABLE CONNEXIONS
    (`id` int, `user_id` int, `date` date)
;

INSERT INTO CONNEXIONS
    (`id`, `user_id`, `date`)
  VALUES
    (1, 1, '2013-04-01'),
    (2, 1, '2017-01-01'),
    (3, 1, '2018-07-01'),
    (4, 2, '2018-09-01'),
    (5, 2, '2018-05-01'),
    (6, 3, '2018-10-01'),
    (7, 1, '2012-01-01'),
    (8, 3, '2016-06-01'),
    (9, 3, '2011-01-01'),
    (0, 1, '2000-01-01')
  ;


SELECT  conn.*, c.first_login
FROM CONNEXIONS AS conn

INNER JOIN (
    SELECT MIN(conn.date) AS first_login, user_id
    FROM CONNEXIONS AS conn
    GROUP BY conn.user_id
) c ON (conn.user_id = c.user_id)

edit: I made some mistake in the query, MAX instead of MIN and client_id instead of user_id.

Upvotes: 2

Views: 70

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

It is worth pointing out that in MySQL 8+, you would use standard window functions:

SELECT c.*,
       MIN(c.date) OVER (PARTITION BY c.user_id) as first_login
FROM CONNEXIONS c;

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • I believe that the first_login will be the minimum connection date, not the maximum.
  • You aliasing was misplaced. I have fixed it.

Try:

SELECT  conn1.*, c.first_login
FROM CONNEXIONS AS conn1
INNER JOIN (
    SELECT MIN(conn2.date) AS first_login, conn2.user_id
    FROM CONNEXIONS AS conn2
    GROUP BY conn2.user_id
) c ON (conn1.user_id = c.user_id)

Result

| id  | user_id | date       | first_login |
| --- | ------- | ---------- | ----------- |
| 1   | 1       | 2013-04-01 | 2000-01-01  |
| 2   | 1       | 2017-01-01 | 2000-01-01  |
| 3   | 1       | 2018-07-01 | 2000-01-01  |
| 4   | 2       | 2018-09-01 | 2018-05-01  |
| 5   | 2       | 2018-05-01 | 2018-05-01  |
| 6   | 3       | 2018-10-01 | 2011-01-01  |
| 7   | 1       | 2012-01-01 | 2000-01-01  |
| 8   | 3       | 2016-06-01 | 2011-01-01  |
| 9   | 3       | 2011-01-01 | 2011-01-01  |
| 0   | 1       | 2000-01-01 | 2000-01-01  |

View on DB Fiddle

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

just use min() instead max() and you need to group by user_id

SELECT  conn.*, c.first_login
FROM CONNEXIONS

INNER JOIN (
    SELECT min(conn.date) AS first_login, user_id
    FROM CONNEXIONS AS conn
    GROUP BY conn.user_id
) c ON (conn.user_id = c.user_id)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated subquery & you need MIN() instead of MAX() :

SELECT CONN.*,
       (SELECT MIN(conn1.date)
        FROM CONNEXIONS conn1
        WHERE conn1.user_id = conn.user_id
       ) AS first_login
FROM CONNEXIONS conn;

Upvotes: 3

Related Questions