database - SQL Select First, Second, Third, Fourth ,Last -

i have membership database number people living in same address. minimise postal expenditure when mailing newsletters, select unique addresses database , add names on same label sticker of people live @ same address. fields have in members table names, addressline1, addressline2, suburb, state, postcode.

i'm happy create new table example additional fields called firstofname, secondofnames, thirdofnames, lastofnames if required. maximum number of people living in same address in database 5.

example of required output on label:

john smith  jane smith  10 high street  beverly hills, nsw 2000 

any appreciated i'm having difficulties one.

thanks heaps, mika

i assume ideal objective 5 names , 1 address in single complex query. example in postgresql. did not specify database believe corollaries exist in oracle , sqlserver. have make own adaptations.

its little messy works. in short use window function rank multiple names per unique address. use clause isolate each name rank. left join select names addresses fewer 5 names.

sample table:

   drop table name_address;     create table name_address    (    first_name character varying (50)    ,last_name character varying (50)    ,addr_line1 character varying (50)    ,city character varying (50)    ,state character varying (2)    ,zip  character varying (5)    )    (oids=true);    alter table name_address owner your_name_here; 

sample data:

   insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'john', 'smith', '10 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'jane', 'smith', '10 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'jeff', 'smith', '12 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'jean', 'smith', '12 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'jenn', 'smith', '12 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'jack', 'smith', '12 high street', 'beverly hills nsw 2000', 'ca', '90210';    insert name_address (first_name, last_name, addr_line1, city, state, zip) select 'josh', 'smith', '12 high street', 'beverly hills nsw 2000', 'ca', '90210'; 

the solution:

   nr (select distinct first_name, last_name, addr_line1, city, state, zip, count(*) over(partition zip, state, city, addr_line1 order zip, state, city, addr_line1, last_name, first_name) name_rank    name_address    group first_name, last_name, addr_line1, city, state, zip    order zip, state, city, addr_line1, name_rank)    ,n1 as(select nr.first_name, nr.last_name, nr.addr_line1, nr.city, nr.state, nr.zip    nr    nr.name_rank = 1)    ,n2 as(select nr.first_name, nr.last_name, nr.addr_line1, nr.city, nr.state, nr.zip    nr    nr.name_rank = 2)    ,n3 as(select nr.first_name, nr.last_name, nr.addr_line1, nr.city, nr.state, nr.zip    nr    nr.name_rank = 3)    ,n4 as(select nr.first_name, nr.last_name, nr.addr_line1, nr.city, nr.state, nr.zip    nr    nr.name_rank = 4)    ,n5 as(select nr.first_name, nr.last_name, nr.addr_line1, nr.city, nr.state, nr.zip    nr    nr.name_rank = 5)    select   distinct n1.first_name        , n1.last_name        , n2.first_name        , n2.last_name        , n3.first_name        , n3.last_name        , n4.first_name        , n4.last_name        , n5.first_name        , n5.last_name        , na.addr_line1        , na.city        , na.state        , na.zip    name_address na    left join n1 on n1.addr_line1 = na.addr_line1 , n1.city = na.city , n1.state = na.state , n1.zip = na.zip    left join n2 on n2.addr_line1 = na.addr_line1 , n2.city = na.city , n2.state = na.state , n2.zip = na.zip    left join n3 on n3.addr_line1 = na.addr_line1 , n3.city = na.city , n3.state = na.state , n3.zip = na.zip    left join n4 on n4.addr_line1 = na.addr_line1 , n4.city = na.city , n4.state = na.state , n4.zip = na.zip    left join n5 on n5.addr_line1 = na.addr_line1 , n5.city = na.city , n5.state = na.state , n5.zip = na.zip; 

here's final output:

"jane";"smith";"john";"smith";"";"";"";"";"";"";"10 high street";"beverly hills nsw 2000";"ca";"90210" "jack";"smith";"jean";"smith";"jeff";"smith";"jenn";"smith";"josh";"smith";"12 high street";"beverly hills nsw 2000";"ca";"90210"