CODEWITHSUNDEEP
phpmysqlsqldatetime
emjay
emjay

Reputation: 1511

select first and last row which have the same value

i have an sql table, which has 3 columns (ID, Name, Datetime).

Here is an short Example:

ID  |     Name    |        Datetime         |
=============================================
1   |  Test 1     |   2014-09-22 22:27:24   |
2   |  Test 223   |   2014-09-22 22:27:24   |
3   |  Test FAS   |   2014-09-22 22:27:24   |
4   |  Test SSA   |   2014-09-22 22:31:18   |
5   |  Test SSA   |   2014-09-22 22:31:18   |
6   |  Test SSA   |   2014-09-22 22:31:18   |
7   |  Test SSA   |   2014-09-22 22:31:18   |
8   |  Test SSA   |   2014-09-22 22:31:18   |

For all Entry's which have the same Datetime, I need the first ID (where this datetime starts) and also the last ID (where this datetime is used the last time).

I'm trying to find the right SQL Command to get this result and generate an array like this:

[0] => Array
        (
            [start] => 1
            [end] => 3
            [date] => 2014-09-22 22:27:24
        )
[1] => Array
        (
            [start] => 4
            [end] => 8
            [date] => 2014-09-22 22:31:18
        )

Could someone give me a solution, how to create this sql command?

Thanks!

Upvotes: 0

Views: 149

Answers (1)

fancyPants
fancyPants

Reputation: 51928

SELECT
MIN(ID) AS start,
MAX(ID) AS end,
COUNT(*) AS how_many,
`Datetime`
FROM
your_table
GROUP BY `Datetime`

Upvotes: 3

Related Questions