CODEWITHSUNDEEP
sqldatabaseoracle-database
name not found
name not found

Reputation: 1

How to fetch first and last value from a table having same value in another attribute?

I have table friendbook_post with attributes text,id,author,type,date_time. id is the primary key and there are many entries in table.

An author can make any number of posts i.e. 'author' can be same in many rows.

Now I want to fetch the first and last post(text) made by every author according to date('date_time') along with date.

I am trying this with many queries but didn't got expected result.

Please help me out with oracle RDBMS.

Upvotes: 0

Views: 54

Answers (2)

user330315
user330315

Reputation: 

select id, text, author, type, date_time,
from (
  select text, id, author, type, date_time,
         row_number() over (partition by author order by date_time) as rn,
         count(*) over (partition by author) as total_author_posts
  from friendbook_post
) t
where rn = 1
   or rn = total_author_posts;

alternatively if you want multiple posts on the same max or min date to be returned:

select id, text, author, type, date_time,
from (
  select text, id, author, type, date_time,
         max(date_time) over (partition by author) as max_date,
         min(date_time) over (partition by author) as min_date
  from friendbook_post
) t
where date_time = max_date
   or date_time = min_date;

Btw: date_time is a horrible name for a column. It doesn't document what kind of "date time" that is. The deletion time? The post time? The publishing time? The "last changed time"?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Perhaps you want something like this:

select fp.*
from friendbook_post fp join
     (select fp.author, min(fp.id) as minid, max(fp.id) as maxid
      from friendbook_post
      group by fp.author
     ) a
     on fp.author = a.author and fp.id in (a.minid, a.maxid);

Upvotes: 1

Related Questions