postgresql - Sql Select rows with a column1 value appearing with more than one column2 value -

i have sql table named table_name follows:

+------------+------------+-----+---------------+ | login_name | session_id | ip  | creation_date | +------------+------------+-----+---------------+ | name1      | sid1       | ip1 | date1         | | name1      | sid1       | ip2 | date2         | | name1      | sid1       | ip2 | date5         | | name2      | sid2       | ip1 | date3         | | name2      | sid2       | ip1 | date4         | +------------+------------+-----+---------------+ 

i want sql code (postgres please) select rows session_id used more 1 ip. example above, result should

+------------+------------+-----+---------------+ | login_name | session_id | ip  | creation_date | +------------+------------+-----+---------------+ | name1      | sid1       | ip1 | date1         | | name1      | sid1       | ip2 | date2         | +------------+------------+-----+---------------+ 

i have code works, i'm rather new , believe can done better (more clear, better performance)

    select table_name.login_name, table_name.session_id, table_name.ip, table_name_grouped.event_date creation_date      table_name     inner join     (         -- session_id - ip pairs         select table_name.session_id, table_name.ip, min(table_name.creation_date) event_date         table_name         group table_name.session_id, table_name.ip     ) table_name_grouped     on table_name.creation_date = table_name_grouped.event_date ,          table_name.session_id = table_name_grouped.session_id ,         table_name.ip = table_name_grouped.ip     table_name.session_id in (         -- session_ids used in multiple ips         select table_name_grouped.session_id                  (             -- session_id - ip pairs             select table_name.session_id, table_name.ip, min(table_name.creation_date) event_date             table_name             group table_name.session_id, table_name.ip         ) table_name_grouped         group table_name_grouped.session_id         having count(table_name_grouped.session_id) > 1     ); 

note following code repetition in code above:

    select table_name.session_id, table_name.ip, min(table_name.creation_date) creation_date              table_name             group table_name.session_id, table_name.ip 

so question is:

1. can solution above improved, how?
2. do see potential problem, example performance?

---

update

i updated example show want 1 row per column1-column2 value pairs. (thanks amazing lighting-fast answers).

select * table_name session_id in (     select session_id     table_name     group session_id     having count(distinct ip) > 1 ) 

the inner select groups session_id , takes having more 1 unique ip. outer selects complete records session_ids.

another possibility joining inner select outer instead of using in().

to pairs smallest date use

select t.*  table_name t join (     select session_id, ip, min(creation_date) dt     table_name     group session_id, ip ) t2 on t.session_id = t2.session_id , t.ip = t2.ip , t.creation_date = t2.dt t.session_id in (     select session_id     table_name     group session_id     having count(distinct ip) > 1 )