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

Postgresql – Very specific select (or not)

I’m having trouble manipulating certain information in a Select. I’ve tried a few ways but with no positive result.

Select:

select
   cpri.sequential,
   cpri.id,
   coalesce(sum(pdr.something), 0) something,
   ...
from
   cpri
inner join cpr on cpr.id = cpri.id
left join pdr on pdr.other_id = cpr.other_id
where
   ...
group by
   cpri.sequential,
   cpri.id,
   ...
order by
   cpri.id desc

I have an output today as follows:

sequential|id |something|other_information
      2637|880| 15000.00|              ...
      2635|880| 15000.00|              ...
      2636|880| 15000.00|              ...
      2638|880| 15000.00|              ...
      2624|876|  6000.00|              ...
      2625|876|  6000.00|              ...
      2611|870|  2000.00|              ...
      2612|870|  2000.00|              ...
      2613|870|  2000.00|              ...
      2614|870|  2000.00|              ...
      2571|858|  5000.00|              ...
      2572|858|  5000.00|              ...
      2569|858|  5000.00|              ...
      2570|858|  5000.00|              ...
       133| 68|  6366.90|              ...
       134| 68|  6366.90|              ...
       130| 66|   120.00|              ...
       129| 66|   120.00|              ...

Target output:

sequential|id |something|other_information
      2637|880| 15000.00|              ...
      2635|880|     0.00|              ...
      2636|880|     0.00|              ...
      2638|880|     0.00|              ...
      2624|876|  6000.00|              ...
      2625|876|     0.00|              ...
      2611|870|  2000.00|              ...
      2612|870|     0.00|              ...
      2613|870|     0.00|              ...
      2614|870|     0.00|              ...
      2571|858|  5000.00|              ...
      2572|858|     0.00|              ...
      2569|858|     0.00|              ...
      2570|858|     0.00|              ...
       133| 68|  6366.90|              ...
       134| 68|     0.00|              ...
       130| 66|   120.00|              ...
       129| 66|     0.00|              ...

If we divide each of the equal ‘ids’ into groups, the ‘something’ column must have its value only in the first row and in the others it must be zero.

Is there a way to do this?

Tags:

2

Answers


  1. 0

    You can do this with a case expression using row_number, something like:

    case when Row_Number() over(partition by cpri.id order by cpri.sequential) = 1 
  then coalesce(sum(pdr.something), 0) else 0
end something
    Login or Signup to reply.
  2. 0

    You can use the analytic function row_number:

    with request as ( 
select  req.* ,
      /******************/
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY something) as row_nbr
from (

select
   cpri.sequential,
   cpri.id,
   coalesce(sum(pdr.something), 0) something,
   ...
from
   cpri
inner join cpr on cpr.id = cpri.id
left join pdr on pdr.other_id = cpr.other_id
where
   ...
group by
   cpri.sequential,
   cpri.id,
) as res 
)

select sequential , id , case when row_nbr = 1 then something ;

    for more information:
    https://www.postgresql.org/docs/current/tutorial-window.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search