CODEWITHSUNDEEP
mysqlsqldatabase
mcktimo
mcktimo

Reputation: 1460

sql for the one entry in each group that meets a condition

I have a query

SELECT ckt, setpt, clock FROM progs 
WHERE feed = "80302" AND day=4 AND clock<"12:15:00" 
ORDER BY ckt, clock DESC

That gets me this:

ckt setpt clock
0 69  06:06:00
0 62  00:30:00
1 57  10:30:00
1 67  04:30:00
1 57  01:30:00
2 69  11:00:00
2 62  00:30:00

How could I modify this query to give me the MAX(clock) for each CKT

In total: Each cktand day has a number of setpt and clock entries. I am looking for the (1 or none) clock record < some_time for each ckt and day. ie

0 69  06:06:00
1 67  10:30:00
2 69  11:00:00

in mysql.

Upvotes: 0

Views: 49

Answers (2)

sgeddes
sgeddes

Reputation: 62831

Sounds like you need to JOIN the table to itself, joining on the MAX(clock):

SELECT p.ckt, p.setpt, p.clock 
FROM progs p
    JOIN (
        SELECT MAX(clock) maxClock, ckt
        FROM progs 
        WHERE feed = "80302" 
            AND day=4 
            AND clock<"12:15:00" 
        GROUP BY ckt
    ) p2 on p.ckt = p2.ckt AND p.clock = p2.maxclock
ORDER BY p.ckt, p.clock DESC

SQL Fiddle Demo (borrowed from other post)

Producing:

0   69  January, 01 1970 06:06:00+0000
1   57  January, 01 1970 10:30:00+0000
2   69  January, 01 1970 11:00:00+0000

Please note row with id 1 is different than your desired results, but match your question's desired results...

Upvotes: 2

Kermit
Kermit

Reputation: 34054

Something like this?

SELECT ckt, MAX(setpt), MAX(clock)
FROM progs 
WHERE feed = "80302" AND day=4 AND clock<"12:15:00" 
GROUP BY ckt
ORDER BY ckt, clock DESC

Result

| CKT | MAX(SETPT) |                     MAX(CLOCK) |
-----------------------------------------------------
|   0 |         69 | January, 01 1970 06:06:00+0000 |
|   1 |         67 | January, 01 1970 10:30:00+0000 |
|   2 |         69 | January, 01 1970 11:00:00+0000 |

See the demo

Upvotes: 4

Related Questions