1

I'm trying to make a query that I can't produce...

This is my table capturainfo

captureid   int(11)
userid      int(11) 
date        date
dateinit    datetime
datefin     datetime

I need to select all rows that have datefin=null and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2.

I just can't do it.

Here are some attempts I've made:

The basic one:

SELECT * FROM capturainfo where date < "2015-02-27" 
AND datefin is null

it gets me all datefin is null, but I can't know if there is one more row in the same day.

SELECT * FROM capturainfo a where date < "2015-02-27" 
AND datefin is null 
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )

... Well this last one doesn't work.

here is the data sample

+-----------+--------+------------+---------------------+---------------------+
|     id    | userid |    date    |      dateinit       |     datefin         |
+-----------+--------+------------+---------------------+---------------------+
|    0001   | 2034   | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
|    0002   | 1789   | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
|    0003   | 1432   | 2015-02-20 | 2015-02-20 12:55:33 |         NULL        |
|    0004   | 1432   | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
|    0005   | 1444   | 2015-02-20 | 2015-02-20 14:41:31 |         NULL        |
+-----------+--------+------------+---------------------+---------------------+

In this case, query should return id 0003 because user 1432 have made a posterior register in the same day

Anybody knows how to do it?

Improve this question
2
  • You should describe your query more specifically (what is the last row of the day- does it depend on date, dateinit, datefin?), maybe adding the minimum amount of example data and expected results. But your query seems to be similar to a common query pattern called "groupwise maximum": dev.mysql.com/doc/refman/5.0/en/…
    – jynus
    Mar 2, 2015 at 18:23
  • Tx for your comment, I will update my question
    – Juliatzin del Toro
    Mar 2, 2015 at 18:30

2 Answers 2

Reset to default
0

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not smaller or equal (<=). You also erroneously compare datefin columns in the subquery when you should compare dateinit columns. It seems you want to get rows from dates older than 2015-02-27:

SELECT * FROM capturainfo a where date < "2015-02-27" 
AND datefin is null 
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.date = b.date AND a.dateinit < b.dateinit AND a.userid = b.userid )
Improve this answer
5
  • I use navicat in mac, and it take ::date like a syntax error. So I delete it, and query doesn't return anything, 0 rows :( date is yyyy-mm-dd and datefin include hours. date may be considerated like redundant but it make the query easier
    – Juliatzin del Toro
    Mar 2, 2015 at 19:23
  • 1
    Yeah, sorry. Made the answer in postgresql syntax by mistake. Corrected in the answer.
    – Simo Kivistö
    Mar 2, 2015 at 19:45
  • Tx ! But still no record returned!
    – Juliatzin del Toro
    Mar 2, 2015 at 20:51
  • Added another correction to the answer: you compared datefin in the subquery when it should have been dateinit.
    – Simo Kivistö
    Mar 3, 2015 at 9:16
  • it seems to works properly ! Thanks for your help !
    – Juliatzin del Toro
    Mar 3, 2015 at 18:15
-1

Try this,

select * from capturainfo where date <= 'your last date' AND datefin='null'

hope it will help.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.