CODEWITHSUNDEEP
sqloracle-database
enkara
enkara

Reputation: 6349

Get records with the newest date in Oracle

I need to find the emails of the last person that performed an action over a post. The database structure is a little bit complicated because of several reasons not important for the case.

SELECT u.address 
FROM text t 
JOIN post p ON (p.pid=t.pid) 
JOIN node n ON (n.nid=p.nid) 
JOIN user u ON (t.login=u.login) 
WHERE n.nid='123456' 
AND p.created IN (
   SELECT max(p.created) 
   FROM text t 
   JOIN post p ON (p.pid=t.pid) 
   JOIN node n ON (n.nid=p.nid) 
   WHERE n.nid='123456');

I would like to know if there is a way to do use the max function or any other way to get the latest date without having to make a subquery (that is almost the same as the main query).

Thank you very much

Upvotes: 0

Views: 104

Answers (2)

user330315
user330315

Reputation:

You can use a window function (aka "analytical" function) to calculate the max date.

Then you can select all rows where the created date equals the max. date.

select address
from (
  SELECT u.address,
         p.created,
         max(p.created) over () as max_date
  FROM text t 
    JOIN post p ON (p.pid=t.pid) 
    JOIN node n ON (n.nid=p.nid) 
    JOIN user u ON (t.login=u.login) 
  WHERE n.nid='123456' 
) t
where created = max_date;

The over() clause is empty as you didn't use a GROUP BY in your question. But if you need e.g. the max date per address then you could use 

max(p.created) over (partition by t.adress) as max_date

The partition by works like a group by

You can also extend that query to work for more than one n.id. In that you you have to include it in the partition:

max(p.created) over (partition by n.id, ....) as max_date

Btw: if n.id is a numeric column you should not compare it to a string literal. '123456' is a string, 123456 is a number

Upvotes: 2

Rachcha
Rachcha

Reputation: 8816

SELECT address
FROM (
SELECT u.address,
       row_number() OVER (PARTITION BY n.nid ORDER BY p.created DESC) AS rn
  FROM text t  JOIN post p ON (p.pid=t.pid) 
       JOIN node n ON (n.nid=p.nid) 
       JOIN user u ON (t.login=u.login) 
 WHERE n.nid='123456' 
)
WHERE rn = 1;

The ROW_NUMBER function numbers the rows in descending order of p.created with PARTITION BY n.nid making separate partitions for row numbers of separate n.nids.

Upvotes: 0

Related Questions