CODEWITHSUNDEEP
sqloracle-database
danio900409
danio900409

Reputation: 265

Maximum date from a given day - sql

I have dates in tables:

2018-09-01 02:22:23
2018-09-01 02:22:25
2018-09-01 02:22:28
2018-09-02 02:22:22
2018-09-02 02:30:00

SELECT * FROM table_name where columnA =  order by 1 desc;

columnA is TIMESTAMP

I would like it to return the maximum date from that day. Does anyone know how this query should look like?

Upvotes: 0

Views: 110

Answers (3)

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

The query should look like -

The below is for a specific date - 

select max(columnA) from table_name
 where trunc(columnA) = date 'yyyy-mm-dd' ;-- this is for oracle
 --      where date(columnA) = 'yyyy-mm-dd' ;-- this is for mysql

A solution could also be, if you require it to be generic, so as to give max timestamp for each day - 

select max(A.columnA) from table_name A
  group by trunc(A.columnA); -- this is for oracle
  -- group by date(A.columnA); -- this is for mysql

Upvotes: 2

Vipul
Vipul

Reputation: 941

Try this query 

SELECT columnA FROM table_name WHERE where trunc(columnA ) = to_date('2018-09-02', 'YYYY-MM-DD') order by columnA DESC LIMIT 1

Upvotes: 1

Devsi Odedra
Devsi Odedra

Reputation: 5322

You can use order by desc and limit

SELECT * FROM table 
   ORDER BY date_field DESC
   LIMIT 1;

Upvotes: 0

Related Questions