MySQL - SELECT query - oldest and youngest date

Question

I need to create one SQL SELECT query. The structure of the table is as follows:

[ID] [PHONE] [description] [DATE]
[1] [600898367] [main] [2016-01-23]
[2] [600898367] [] [2016-01-24]
[3] [600898367] [] [2016-01-26]
[4] [600898367] [] [2016-01-28]
[5] [662349093] [main] [2016-01-10]
[6] [662349093] [] [2016-01-21]
[7] [662349093] [] [2016-01-30]
[8] [662349093] [] [2016-01-31] 

You have different entries grouped by the same phone number. The first (oldest) event is indicated by the [main] icon. There are no two identical numbers with the [primary] flag. I want to select each [main] entry and another junior with the same phone number, so the result should contain entries 1,4,5,8.

Please, help.

Answer 1

WHERE, main. MAX, JOIN, . , UNION ALL, .

-- Get the main records first
SELECT *
FROM tbl
WHERE description = 'main'

UNION ALL

-- Get the most recent records
SELECT b.*
FROM (
    SELECT
        t.PHONE,
        MAX(DATE) AS MaxDate
    FROM tbl t
    GROUP BY PHONE
) a
INNER JOIN tbl b -- Do a JOIN to get the additional columns
    ON b.PHONE = a.PHONE
    AND b.DATE = a.MaxDate

Answer 2

;)

SQL Fiddle

MySQL 5.6 :

CREATE TABLE table1
    (`ID` int, `PHONE` int, `description` varchar(4), `DATE` varchar(11))
;

INSERT INTO table1
    (`ID`, `PHONE`, `description`, `DATE`)
VALUES
    (1, 600898367, 'main', '2016-01-23'),
    (2, 600898367, NULL, '2016-01-24'),
    (3, 600898367, NULL, '2016-01-26'),
    (4, 600898367, NULL, '2016-01-28'),
    (5, 662349093, 'main', '2016-01-10'),
    (6, 662349093, NULL, '2016-01-21'),
    (7, 662349093, NULL, '2016-01-30'),
    (8, 662349093, NULL, '2016-01-31')
;

1:

select t.*
from table1 t
inner join (
    select `PHONE`, max(`DATE`) as `DATE` from table1 group by `PHONE`
) t1 on t.`PHONE` = t1.`PHONE` and (t.`DATE` = t1.`DATE` or t.`description` = 'main')
order by t.`ID`

:

| ID |     PHONE | description |       DATE |
|----|-----------|-------------|------------|
|  1 | 600898367 |        main | 2016-01-23 |
|  4 | 600898367 |      (null) | 2016-01-28 |
|  5 | 662349093 |        main | 2016-01-10 |
|  8 | 662349093 |      (null) | 2016-01-31 |

Answer 3

:

SELECT t1.*, t3.*
FROM mytable AS t1
LEFT JOIN (
   SELECT PHONE, MAX(date) AS max_date
   FROM mytable 
   GROUP BY PHONE
) AS t2 ON t1.PHONE = t2.PHONE
LEFT JOIN mytable AS t3 ON t1.PHONE = t3.PHONE AND t2.max_date = t3.`date`
WHERE t1.description = 'main'

Answer 4

GROUP BY PHONE, description, 4 .

-, GROUP_CONCAT. ORDER BY, .

, , SUBSTRING_INDEX.

SELECT
  SUBSTRING_INDEX(GROUP_CONCAT(`PHONE` ORDER BY `DATE`), ',', 1) AS `PHONE`,
  description
FROM table1
GROUP BY `PHONE`, `description`;

---

Fiddle

Answer 5

Try the following query.

SELECT 
  A.* 
FROM
  `old_young` A 
  INNER JOIN 
    (SELECT 
      MIN(`DATE`) AS Res 
    FROM
      old_young 
    WHERE description = 'main' 
    GROUP BY PHONE 
    UNION
    ALL 
    SELECT 
      MAX(`DATE`) AS Res 
    FROM
      old_young 
    WHERE description = '' 
    GROUP BY PHONE) B 
    ON A.DATE = B.Res ;  

Check FIDDLE