SQL - get the first inserted data based from up_time

Question

I'm trying to get the very first datetime record from a returned table that happens to store multiple title using this query:

(this is quite long because i am joining many tables..)

SELECT import.title, a_queue.ins_date, a_queue.start_date, a_queue.end_date, pData.up_time
FROM queuedb.a_queue
LEFT JOIN playDB.base_data playlistData ON playlistData.id = a_queue.playlist_id
LEFT JOIN dDb.import_data import ON playlistData.description = import.id
LEFT JOIN dDb.import_proc pData ON pData.a_id = import.a_id
WHERE a_queue.organization = 'sample' AND pData.unique_id is not NULL AND a_queue.ins_date BETWEEN '2016-09-01 00:00:00' AND '2016-09-30 23:59:59';

With this query, it will return this result:

It should have been just these two..

+---------+--------------------+----------+--------+--------------------+
|title    |ins_time            |start_date|end_date|up_time             |
+---------+--------------------+----------+--------+--------------------+
|Testing 1|2016-09-05 13:45:19 |NULL      |NULL    |2016-09-21 03:35:08 |
|Testing 4|2016-09-05 15:08:25 |NULL      |NULL    |2016-09-21 02:00:22 |
+---------+--------------------+----------+--------+--------------------+

How do i achieve this result..

[Groupwise maximum](http://stackoverflow.com/questions/15211479/groupwise-maximum) or [Mysql Documentation - The Rows Holding the Group-wise Maximum of a Certain Column](http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html). To get the first row instead of the last row, you will of course have to take `min()` instead of `max()`, or exchange `<` and `>`. — Solarflare, Sep 28 '16 at 07:24

score 0 · Answer 1 · answered Sep 28 '16 at 09:53

Try this,

if you want minimum up_time use, MIN(up_time) and GROUP BY import.title

SELECT import.title, a_queue.ins_date, a_queue.start_date, a_queue.end_date, MIN(pData.up_time)
    FROM queuedb.a_queue
    LEFT JOIN playDB.base_data playlistData ON playlistData.id = a_queue.playlist_id
    LEFT JOIN dDb.import_data import ON playlistData.description = import.id
    LEFT JOIN dDb.import_proc pData ON pData.a_id = import.a_id
    WHERE a_queue.organization = 'sample' AND pData.unique_id is not NULL AND a_queue.ins_date BETWEEN '2016-09-01 00:00:00' AND '2016-09-30 23:59:59' GROUP BY import.title;

SQL - get the first inserted data based from up_time

1 Answers1