CODEWITHSUNDEEP
mysqlgroup-by
Sisuman
Sisuman

Reputation: 29

MySQL: Getting ascending dates grouped by another column

I have a table that holds name and expiry dates of components in columns, third column holds address in which frame (frame_id) the component is installed. One frame has many (or none) components installed.

I would need to get a listing of components expiring first in each frame. Just one row per frame. E.g. if i have 50 frames, i would need dates, names and id:s of first expiring component for each frame. A simple GROUP BY does not seem to do it.

Table1
expiry     | name        | frame_id | component_id
-------------------------------------------------
2013-10-03 | component_1 | 0320     | 127
2013-11-12 | component_1 | 0275     | 461
2013-11-22 | component_4 | 0320     | 011
2014-01-05 | component_2 |          | 224
2014-01-16 | component_3 | 0275     | 782

Listing should be like this:

0320 2013-10-03, expiring component: component_1 id: 127
0275 2013-11-12, expiring component: component_1 id: 461

Only unique frames listed with expiry date of first expiring component

Thanks!

Upvotes: 1

Views: 49

Answers (2)

Anda Iancu
Anda Iancu

Reputation: 530

sql fiddle is not working so i will add here the code.

CREATE TABLE `TableTT1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `expiry` date NOT NULL,
  `name` varchar(16) NOT NULL,
  `frame_id` int(11) DEFAULT NULL,
  `component_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;


INSERT INTO `TableTT1` (`expiry`, `name`, `frame_id`, `component_id`)
VALUES
('2013-10-03', 'component_1', 0320, 127), 
('2013-11-12', 'component_1', 0275, 461), 
('2013-11-22', 'component_4', 0320, 011), 
('2014-01-05', 'component_2',  null, 224), 
('2014-01-16', 'component_3', 0275, 782);


    SELECT * FROM TableTT1;  

+----+------------+-------------+----------+--------------+
| ID | expiry     | name        | frame_id | component_id |
+----+------------+-------------+----------+--------------+
|  1 | 2013-10-03 | component_1 |      320 |          127 |
|  2 | 2013-11-12 | component_1 |      275 |          461 |
|  3 | 2013-11-22 | component_4 |      320 |           11 |
|  4 | 2014-01-05 | component_2 |     NULL |          224 |
|  5 | 2014-01-16 | component_3 |      275 |          782 |
+----+------------+-------------+----------+--------------+


SELECT 
    MIN(expiry) AS expFirst, 
    GROUP_CONCAT(frame_id, ' ', expiry, ' ', name, ' ', component_id) AS Details
FROM TableTT1 
GROUP BY frame_id, name 
ORDER BY expFirst;

+------------+--------------------------------+
| expFirst   | Details                        |
+------------+--------------------------------+
| 2013-10-03 | 320 2013-10-03 component_1 127 |
| 2013-11-12 | 275 2013-11-12 component_1 461 |
| 2013-11-22 | 320 2013-11-22 component_4 11  |
| 2014-01-05 | NULL                           |
| 2014-01-16 | 275 2014-01-16 component_3 782 |
+------------+--------------------------------+

Hope this helps.

Upvotes: 1

Abhishek Jain
Abhishek Jain

Reputation: 2607

Try this:

SELECT
    MIN(expiry), frame_id
FROM Table1
GROUP BY frame_id

Upvotes: 0

Related Questions