Selecting the max date out of a text group This query selects the questions with the same subject

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;


It gives me result something like 

new subject message_id product_id si question_date
subject1    65299   0   5   2018-03-11 11:03:02
subject1    64522   0   5   2018-03-10 14:53:05
subject1    64511   0   5   2018-03-10 14:51:18
subject2    65214   0   5   2018-03-11 15:40:36
subject2    64625   0   5   2018-03-11 13:28:25
subject2    65245   0   5   2018-03-11 13:03:41
subject2    64365   0   5   2018-03-11 12:49:01
subject3    65007   0   5   2018-03-29 19:58:15
subject3    64043   0   5   2018-03-29 18:42:20
subject4    65562   0   5   2018-03-09 14:23:44


But just want to get the recent(only 1 ) subject1,subject2,subject3,subject4

I added these lines to get it

  WHERE question_date IN (
    select max(question_date) FROM messages
  )


Which has become

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x
  WHERE question_date IN (
    select max(question_date) FROM messages
  )

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;


But it doesn't return me 1 record from each, but no result. What am I missing?
Also it would have been great if you can provide a more performant query.

This is my table description

                                                Table "public.messages"
     Column      |              Type              | Collation | Nullable |                   Default
-----------------+--------------------------------+-----------+----------+----------------------------------------------
 message_id      | integer                        |           | not null | nextval('messages_message_id_seq'::regclass)
 product_id      | integer                        |           |          |
 subject         | text                           |           |          |
 body            | text                           |           | not null |
 customer_id     | integer                        |           | not null |
 message_type    | integer                        |           | not null |
 question_date   | timestamp(0) without time zone |           | not null |
 seller_id       | integer                        |           | not null |
 deleted_at      | timestamp(0) without time zone |           |          |
 created_at      | timestamp(0) without time zone |           |          |
 updated_at      | timestamp(0) without time zone |           |          |
 market_place_id | bigint                         |           | not null |
 answer_source   | integer                        |           |          |
 answer_id       | bigint                         |           |          |
 hide_question   | boolean                        |           | not null | false
You need to choose max(question_date) per subject. Something like:

WHERE question_date IN (
    select max(question_date) 
    FROM messages
    WHERE subject = a.subject 
)


Another option is to enumerate using OLAP functions and choose most recent subject using that:

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id as si

       , row_number() over (partition by a.subject
                            order by question_date desc) as rn   

       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

WHERE rn = 1 

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;


The first alternative will return several rows per subject in case they have the same question_date. The second alternative will return only one 
 row per subject. Using rank() instead of row_number() makes the second alternative to behave like the first one.

You may have to add another level of nesting to take into consideration that several subjects may map to the same new_subject:

select new_subject, message_id, product_id, si, question_date
from (
    select
      new_subject,
      message_id,
      product_id,
      si,
      question_date,            
      row_number() over (partition by new_subject
                         order by question_date desc) as rn   
    from (
           select
             trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
             *,
             a.seller_id as si
           from messages a
             INNER JOIN sellers b
               ON a.seller_id = b.seller_id
           WHERE a.product_id = 0
         ) as x
) where rn = 1
ORDER BY new_subject, question_date DESC;