CODEWITHSUNDEEP
mysqlsql
user2710234
user2710234

Reputation: 3225

using min and DATE() in SQL

I am running an SQL Query and i want to use min() and DATE()

i tried:

and min(DATE(datetime)) = '".date("Y-m-d")."'

but that does not work, what should i be using?

the full query i have tried is:

select 
  min(datetime) as d, 
  ticketnumber 
from ticket_updates 
where type = 'update' 
and min(DATE(datetime)) = '".date("Y-m-d")."' 
group by ticketnumber

i have also tried:

select 
  min(datetime) as d, 
  ticketnumber 
from ticket_updates 
where type = 'update' 
and min(datetime) >= '".date("Y-m-d 00:00:00")."' 
and min(datetime) <= '".date("Y-m-d 23:59:59")."'
group by ticketnumber

but i get an error saying:

Invalid use of group function

Upvotes: 0

Views: 492

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want the earliest date time, then use a having clause:

select min(datetime) as d, ticketnumber 
from ticket_updates 
where type = 'update' 
group by ticketnumber
having min(DATE(datetime)) = '".date("Y-m-d")."'

These seems rather strange as a construct, because the min is going to get the date you are entering. All the work is in the filtering.

Upvotes: 1

vp_arth
vp_arth

Reputation: 14982

Wrap your query to another:

SELECT d, ticketnumber FROM(
  select 
  min(datetime) as d, 
  ticketnumber 
  from ticket_updates 
  where type = 'update' 
  group by ticketnumber
) t
WHERE d between '...' and '...'

FIDDLE

Upvotes: 0

crthompson
crthompson

Reputation: 15865

There is no need to use the min function in your where clause.

I've also changed the where to use between for readability.

select 
  min(datetime) as d, 
  ticketnumber 
from ticket_updates 
where type = 'update' 
and datetime between'".date("Y-m-d 00:00:00")."' 
                and '".date("Y-m-d 23:59:59")."'
group by ticketnumber

This code will find the lowest date/time for each ticket number on the date provided.

Check out this FIDDLE

Upvotes: 1

sql_dev1802
sql_dev1802

Reputation: 35

If you would like to get the min date you need to have a column to group by. For example, 

Select id, Min(Date) as Min_date
from mytable group by id

Can you provide more details so that we can try to resolve this?

Upvotes: -1

Related Questions