Thread: query, probably needs window functions
given, let's say: create table person (id int not null, name varchar); create table phone (id int not null, person_id int not null, number varchar); select person.*, phone.number from person join phone on (person.id = phone.person_id) order by... How would you get results where only the first row for a person was filled in, with rest of that person's phones showingblanks for those columns? I'm guessing that window functions provide this capability, but I don't know how. -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/
On Friday, May 22, 2020, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
given, let's say:
create table person (id int not null, name varchar);
create table phone (id int not null, person_id int not null, number varchar);
select person.*, phone.number from person join phone on (person.id = phone.person_id) order by...
How would you get results where only the first row for a person was filled in, with rest of that person's phones showing blanks for those columns? I'm guessing that window functions provide this capability, but I don't know how.
If a left join doesn’t give you the answer you want you should probably provide exact input and output data that you are working with/toward.
David J.
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;
On Fri, May 22, 2020 at 12:38 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 current name value to the lag value and null it out if it's the same.
This. I misread the question. You might also consider just outputting one row per person and output the related phone numbers using string_agg(phone.number, E'\n')
David J.
> 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;