0

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

1 Answer 1

1

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;
8
  • This table doesn't have subject_id since this is a message table. I changed a.subject_id to a.message_id(primary key ) but still give me more than 1 results with same subject @Lennart
    – SNaRe
    Apr 11, 2018 at 14:56
  • Yes I tried. It also shows me duplicate "new_subject" values
    – SNaRe
    Apr 11, 2018 at 15:11
  • I add table description to the OP, if it may help you.
    – SNaRe
    Apr 11, 2018 at 15:14
  • 1
    It would help if you added CREATE TABLE and INSERT statements or a db-fiddle.com. Apr 11, 2018 at 15:40
  • Could you please tell me how can I obtain them quickly with commands? I will add to db-fiddle afterwards. Thanks
    – SNaRe
    Apr 11, 2018 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.