How to extract multiple columns, one being unique values from MySQL

Question

I'm having a blonde moment, I have a timestamp and value column that I want to query from a PHP MySQL table "mytable". However I would like only get one value and the corresponding timestamp for the one value. For example:

value,timestamp
23,2016-09-01 00:04:05 
23,2016-09-01 00:04:05 
23,2016-09-02 01:04:05 < --- extract this
24,2016-09-02 01:05:13
24,2016-09-03 01:04:24 
27,2016-09-03 03:24:34 
27,2016-09-04 04:24:42
27,2016-09-05 05:54:12 
27,2016-09-06 00:04:05
27,2016-09-07 00:04:05 < --- extract this
28,2016-09-08 00:04:05 < --- extract this

I want to be able to extract from the above table, onevalue and it's corresponding timestamp

value,timestamp
23,2016-09-02 01:04:05
27,2016-09-07 00:04:05
28,2016-09-08 00:04:05

Q: What would be the query look like?

How are those unique? I don't see any pattern in what you want to output. — Sami Kuhmonen
– Sami Kuhmonen, Commented Sep 15, 2016 at 5:12
I also don't see any pattern, but this looks like this might be a gaps and islands problem. Have you tried anything yet? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 15, 2016 at 5:12
@SamiKuhmonen, my apologies, I fixed up my question, I just want the latest time stamp with a one-off value i.e no duplicate values... again my apologies — 3kstc
– 3kstc, Commented Sep 15, 2016 at 5:29

sh88 · Accepted Answer · 2016-09-15 05:43:20Z

1

Try this

SELECT
    value,
    MAX(TIMESTAMP)
FROM
    yourTable
GROUP BY
    value

answered Sep 15, 2016 at 5:43

sh88

1063 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract multiple columns, one being unique values from MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related