CODEWITHSUNDEEP
sqloracle-databasedategreatest-n-per-group
AlwaysALearner
AlwaysALearner

Reputation: 6690

Finding MAX(DATE) value in SQL

I am new to SQL, hence this question-

I have a DATE column in my table and my query is supposed to fetch the value with the most recent DATE. I have realized that I can achieve it through any of the following ways-

  1. SELECT TRUNC(MAX(createddatetm)) cdt FROM (SELECT TO_TIMESTAMP(TO_CHAR(createddate, 'Month dd, yyyy hh:mi AM'), 'Month dd, yyyy hh:mi AM') as createddatetm FROM comments WHERE commentid = '1234');

  2. SELECT trunc(MAX(TO_TIMESTAMP(to_char(createddate, 'Month dd, yyyy hh:mi AM'), 'Month dd, yyyy hh:mi AM'))) FROM (SELECT createddate FROM comments WHERE commentid = '1234');

  3. SELECT TRUNC(MAX(createddatetm)) cdt FROM (SELECT TO_CHAR(createddate, 'Month dd, yyyy hh:mi AM') as createddatetm FROM comments WHERE commentid = '1234');

  4. SELECT trunc(MAX(to_char(createddate, 'Month dd, yyyy hh:mi AM'))) FROM (SELECT createddate FROM comments WHERE commentid = '1234');

HERE IS MY QUESTION-
Is there any other simpler way using which I can achieve this? Or should I should I use any of these I've mantioned? Also, are there any differences between these queries with respect to performance? I don't think there are, but I need to confirm that once.

Upvotes: 1

Views: 2937

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Why have nested Select statements when you are just looking for one simple value?

select  trunc( max( createddate )) cdt
from    comments
where   commentid = '1234';

Upvotes: 0

user330315
user330315

Reputation:

Why are you converting a date to a varchar to convert it back to a date/timestamp again? You can just compare/use the date value stored in the database.

Your problem can easily be solved using a window function:

select *
from ( 
  select c.*, 
         row_number() over (order by createddate desc) as rn
  from comments
  where commentid = 1234
) t
where rn = 1;

alternatively using max()

select *
from ( 
  select c.*, 
         max(createddate) over () as max_createddate
  from comments
  where commentid = 1234
) t
where max_createddate = createddate;

or if you need that for multiple commentid values:

select *
from ( 
  select c.*, 
         row_number() over (partition by commentid order by createddate desc) as rn
  from comments
) t
where rn = 1;

Upvotes: 7

Related Questions