0

This is the workflow I was working after...

Get data between time and time -> sort by newest -> filter out the duplicates phonenumbers -> search for three different variables string -> print result using php.

First I used this, however it didn't sort by latest record:

  SELECT id, phone, text 
  FROM phones 
  WHERE timestamp BETWEEN '2016-10-01 00:00:00' AND '2016-10-06 23:59:00' 
  GROUP BY user 
  ORDER BY timestamp DESC

After searching and testing I got it to work with a subquery:

SELECT id, phone, text 
FROM phones WHERE timestamp IN(SELECT
                       max(timestamp) 
                       FROM phones 
                       GROUP BY phone )  
ORDER BY timestamp DESC

However now I'm not sure where I shall put the function: 

BETWEEN '2016-10-01 00:00:00' AND '2016-10-06 23:59:00'

I tried in the subquery and outside in the main. Is it possible to do this?

user229044
  • 232,980
  • 40
  • 330
  • 338
user3363100
  • 19
  • 1
  • In your first query you have a GROUP BY user but you don't have an aggregate function, i.e. you are not doing a count, sum, average, etc. In your second query you will only match on those rows that have the max timestamp. You seem to want all the records sorted desc and that second query won't get you that. If you can provide some sample data in a table that would help. – mba12 Oct 05 '16 at 17:50
  • See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the newest row for each phone. Just add the `BETWEEN` condition to the subqueries. – Barmar Oct 05 '16 at 18:24
  • thanks for the link, it worked after doing like this. SELECT id,phone,text,timestamp,MAX(timestamp) FROM phones WHERE timestamp BETWEEN '2016-10-10 20:43:33' AND '2016-10-10 22:44:52' GROUP BY phone ORDER BY timestamp DESC – user3363100 Oct 10 '16 at 18:22

1 Answers1

0

Try using str_to_date 

SELECT id, phone, text 
FROM phones 
WHERE date(timestamp)  BETWEEN  str_to_date( '2016-10-01' ,'%Y-%m-%d') 
                              AND str_to_date( '2016-10-06'  '%Y-%m-%d') 
ORDER BY timestamp DESC
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107