Re: query, probably needs window functions

[Scott Ribe]

> On May 22, 2020, at 1:37 PM, Michael Lewis  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;

Re: query, probably needs window functions

[David G. Johnston]

On Fri, May 22, 2020 at 12:38 PM Michael Lewis  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.

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;

Re: query, probably needs window functions

[David G. Johnston]

On Friday, May 22, 2020, Scott Ribe  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.