> On May 22, 2020, at 1:37 PM, Michael Lewis <mlewis@entrata.com> wrote:
>
> I believe something like this is what you want. You might be able to do it without a sub-query by comparing the
currentname value to the lag value and null it out if it's the same.
> ...
Thanks, that's what I needed! (And better than using lag in my case because there's 4 tables in the joins and many more
columnsinvolved. The repetition of "case when lag(...)..." would be really noisy, but it's good to know of that
possibilityanyway.)
One correction, just for posterity if someone else searches this question, the answer was missing "over", should have
been:
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() over 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;