Agfdhyk

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.

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



INNER JOIN (

    SELECT MAX(conn.date) AS first_login, user_id

    FROM CONNEXIONS AS conn

    GROUP BY conn.client_id

) c ON (conn.user_id = c.client_id)

• 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

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;

just use min() instead max()

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.client_id)