query, probably needs window functions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

query, probably needs window functions

Scott Ribe-2
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.

--
Scott Ribe
[hidden email]
https://www.linkedin.com/in/scottribe/





Reply | Threaded
Open this post in threaded view
|

Re: query, probably needs window functions

David G Johnston
On Friday, May 22, 2020, Scott Ribe <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: query, probably needs window functions

Michael Lewis
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;

Reply | Threaded
Open this post in threaded view
|

Re: query, probably needs window functions

David G Johnston
On Fri, May 22, 2020 at 12:38 PM Michael Lewis <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: query, probably needs window functions

Scott Ribe-2
In reply to this post by Michael Lewis
> On May 22, 2020, at 1:37 PM, Michael Lewis <[hidden email]> 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.
> ...

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 columns involved. The repetition of "case when lag(...)..." would be really noisy, but it's good to know of that possibility anyway.)

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;