PLSQL query for getting all records with MAX date

Question

I'm working on a table which has more than 10 columns. One of the column name is ASAT which is of type DATE(Format is yyyy-mm-dd HH:MM:SS:mmm).

I'm looking for a sql query which returns all records of max date. Trying to use that query in java for JDBC call.

I tried this: Select * from tablename where ASAT in (select MAX(ASAT) from tablename).

But it is not returning any records.

Any help is really appreciated.Thanks

Do you return a value as expected when running only the subquery? — Chris Hep, Nov 01 '16 at 21:48

score 0 · Answer 1 · answered Nov 02 '16 at 14:59

0

How about:

SELECT MAX(Asat) FROM TableA;
SELECT MAX(Asat) FROM TableA GROUP BY Asat;

answered Nov 02 '16 at 14:59

Tenzin

2,415
2
23
36

That will return MAX ASAT value from table right? But i'm looking for all records with that MAX ASAT value. THANKS! – universe Nov 03 '16 at 18:16

score 0 · Answer 2 · answered Nov 05 '16 at 15:15

0

When you self join, I suggest aliasing each copy of the table. Personally I use the table letter with a number afterwards in case I need to track it for larger queries.

Select * 
from tablename t1 
where t1.ASAT = (
    select MAX(t2.ASAT) 
    from tablename t2
)

answered Nov 05 '16 at 15:15

Zynon Putney II

675
4
12

Error: Server Exception [36e69a2e-f10d-4c74-8454-273d43c35cdb-5][line 4:11] no viable alternative at input 'MAX' Getting that error when using that query. – universe Nov 14 '16 at 20:41
Please see https://stackoverflow.com/questions/14897599/what-causes-no-viable-alternative-at-input-none-error-with-cassandra-cql .. That error is not due to malformed SQL. – Zynon Putney II Jul 28 '17 at 13:30

score 0 · Answer 3 · answered Jan 30 '20 at 17:50

I believe you are looking for something like this if I'm understanding you. First build a CTE containing the primary key and the MAX(ASAT). Then join to it, selecting where the primary key matches the primary key of the row with the MAX(ASAT). Note your "ID" may have to be more than one column.

with tbl_max_asat(id, max_asat) as (
  select id, max(asat) max_asat
  from tablename
  group by id
)
select * 
from tablename t
  join tbl_max_asat tma
    on t.id = tma.id;

This old post just popped up because it was edited today. Maybe my answer will still help someone. :-)

PLSQL query for getting all records with MAX date

3 Answers3