PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to group by and select only the specific item in a group

test_run_id test_id suite_id exe_length message
4201 32 39 11 Hub Status does1
4201 32 39 17 Hub Status does3
4201 32 39 17 Hub Status does4
4201 32 39 18 Hub Status does7
4200 32 39 11 Hub Status does8
4197 32 39 11 Hub Status does0
4196 32 39 17 Hub Status doesn14
4196 32 39 18 Hub Status does134
4195 NULL 31 14 Hub Status does123
4187 7 12 35 Hub Status d34567
4187 7 12 35 Hub Status 345566

I have the table above, how can I select for each matching test_run_id, the test_run_id and suite_id of the message with the minimum length?

Example:
for test_run_id=4201, test_id=32, suite_id=39, I want the message where exe_length is minimum (11). i.e message = "Hub Status does1"

when the column values are hardcoded it’s easy:

select  message
    from log_message_view_uat
    where test_run_id= 4201 and test_id=32 and suite_id=39 and exe_length = (select min(exe_length) from log_message_view_uat
    where test_run_id= 4201 and test_id=32 and suite_id=39)

How can I get the same message per column parameter in the database?

Tags:

4

Answers


  1. 0

    Use row_number windows function if wanting just one per group (even if ties). If you want to keep the ties, then use dense_rank.

    --if wanting just one even if ties
select 
 test_run_id, 
 test_id, 
 suite_id, 
 exe_length, 
 message
from (
    select *, 
      row_number() over (partition by test_run_id, test_id, suite_id order by exe_length) as rn
    from log_message_view_uat
    )z
where rn = 1
order by 1
    test_run_id test_id suite_id exe_length message
    4187 7 12 35 Hub Status 345566
    4195 null 31 14 Hub Status does123
    4196 32 39 17 Hub Status doesn14
    4197 32 39 11 Hub Status does0
    4200 32 39 11 Hub Status does8
    4201 32 39 11 Hub Status does1 
    --if wanting ties
select 
 test_run_id, 
 test_id, 
 suite_id, 
 exe_length, 
 message
from (
    select *, 
      dense_rank() over (partition by test_run_id, test_id, suite_id order by exe_length) as rn
    from log_message_view_uat
    )z
where rn = 1
order by 1
    test_run_id test_id suite_id exe_length message
    4187 7 12 35 Hub Status 345566
    4187 7 12 35 Hub Status d34567
    4195 null 31 14 Hub Status does123
    4196 32 39 17 Hub Status doesn14
    4197 32 39 11 Hub Status does0
    4200 32 39 11 Hub Status does8
    4201 32 39 11 Hub Status does1

    fiddle

    Login or Signup to reply.
  2. 0

    You can do it without an inner select with first_value

        select 
        test_run_id,
        test_id,
        suite_id, 
        exe_length,
        message,
        first_value(message) over (partition by test_run_id, test_run_id, suite_id order by exe_length) as min_len
    from log_message_view_uat
    order by test_run_id desc

    The relevant function is:

    first_value(message) -- we want the first value of a group of values
  over (
      partition by test_run_id, test_run_id, suite_id -- this works basically like a group by for the function without combining all the rows into one.
      order by exe_length -- in order for first_value to make any sense we better define an order.
) as min_len

    For more information about first_value

    See the DB-Fiddle

    Login or Signup to reply.
  3. 0

    try using GROUP BY and HAVING

    select  message
from log_message_view_uat
where test_run_id= 4201 and test_id=32 and suite_id=39 
GROUP BY test_run_id
HAVING exe_length = MIN(exe_length);
    Login or Signup to reply.
  4. 0

    Distinct on does exactly that: demo

    select distinct on (1,2,3) *
from log_message_view_uat
order by 1,2,3,exe_length;
    test_run_id test_id suite_id exe_length message
    4187 7 12 35 Hub Status 345566
    4195 null 31 14 Hub Status does123
    4196 32 39 17 Hub Status doesn14
    4197 32 39 11 Hub Status does0
    4200 32 39 11 Hub Status does8
    4201 32 39 11 Hub Status does1

    1,2,3 are column numbers, which means for each combination of test_run_id, test_id and suite_id you get one row, and it will be the first one in the order of exe_length, ascending.

    If you only want the message, you’ll need explicit column names:

    select distinct on(test_run_id,test_id,suite_id)message
from log_message_view_uat
order by test_run_id,test_id,suite_id,exe_length;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search