Re: query, probably needs window functions

From: Michael Lewis <mlewis(at)entrata(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: query, probably needs window functions
Date: 2020-05-22 19:37:59
Message-ID: CAHOFxGpwuPCSmuSiD9yKDG_rTbvmU156LX3ckRdEo5ixy-NM9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I believe something like this is what you want. You might be able to do it
without a sub-query by comparing the current name value to the lag value
and null it out if it's the same.

select
case when row_number = 1 then id end AS id,
case when row_number = 1 then name end as name,
phone.number
from(
select person.id, person.name, phone.number, row_number() partition
by( phone.person_id order by phone.number ) as row_number
from person
join phone on person.id = phone.person_id
) AS sub
order by name, row_number;

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-05-22 20:10:46 Re: query, probably needs window functions
Previous Message Andrus 2020-05-22 18:54:34 Re: Query returns no rows in pg_basebackup cluster