Need help on a query within a query. 1

[eyetry]

Simple table looks somehting like:

key | name | addr

There are duplicate name address combos. I want the value of the key for the 'first' instance of rows that are either single occurances or multiple. So, if the data looks like....

1 | john | main st
2 | mary | 1st st
3 | peter | charles ave
4 | peter | charles ave
5 | john | main st
6 | sue | south ave

I want to extract keys 1,2,3 and 6.

I assume I'd do this with a query in a query but not an DB developer.

"Select distinct(name), addr from table" gives me the distinct names but not the key.

"Select distinct(name), addr, key from table" gives me everything because the key is always unique.

I think I want something like....

select min(a.key) from table a
where (select (distinct(a.name), a.addr from table a);

But this is wrong because I think I need to have the where = somthing else. But this doesn't work either...

select min(a.key) from table a
where (select (distinct(a.name), a.addr from table a) = select (distinct(b.name), b.addr from table b;

Pobably going down the wrong road altogether... so please help the novice again!

 

[SantaMufasa]

Eyetry,

You are soooooo close ! You intuition is great. Here is the solution to your need:

select min(key) key,name,addr
from eyetry
group by name, addr
order by key
/

  KEY NAME       ADDR
----- ---------- ------------
    1 john       main st
    2 mary       1st st
    3 peter      charles ave
    6 sue        south ave

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

Worked great for the sample and for my original table. Once I had it working though it turned out I need to create more detail to the table in question and thing's got more complicated....

Bought the DBA a soda (I hate to burn up favors) and he helped me put a new query together. I then added to it ending up with something like.....

SELECT *
FROM
eyetry t
where sub_loop_id = (select min(KEY) from eyetry i where
i.SSN = t.SSN and
nvl(i.LAST_NAME, 'x') = nvl(t.LAST_NAME, 'x') and
nvl(i.FIRST_NAME, 'x') = nvl(t.FIRST_NAME, 'x') and
nvl(i.INIT, 'x') = nvl(t.INIT, 'x') and
nvl(i.ADDRESS1, 'x') = nvl(t.ADDRESS1, 'x') and
nvl(i.ADDRESS2, 'x') = nvl(t.ADDRESS2, 'x') and
nvl(i.CITY, 'x') = nvl(t.CITY, 'x') and
nvl(i.STATE, 'x') = nvl(t.STATE, 'x') and
nvl(i.ZIP, 'x') = nvl(t.ZIP, 'x') and
nvl(i.MARITAL_STATUS, 'x') = nvl(t.MARITAL_STATUS, 'x') and
nvl(i.GROUP_NO, 'x') = nvl(t.GROUP_NO, 'x') and
nvl(i.SUBGROUP_NO, 'x') = nvl(t.SUBGROUP_NO, 'x') and
nvl(i.COV1_PLAN, 'x') = nvl(t.COV1_PLAN, 'x') and
nvl(i.COV1_ELIG_TYPE, 'x') = nvl(t.COV1_ELIG_TYPE, 'x') and
nvl(i.COV1_EFF_DATE, 'x') = nvl(t.COV1_EFF_DATE, 'x') and
nvl(i.COV1_END_DATE, 'x') = nvl(t.COV1_END_DATE, 'x'))

 

[eyetry]

sorry typo..... was more like....

SELECT *
FROM
eyetry t
where KEY = (select min(KEY) from eyetry i where
i.SSN = t.SSN and
nvl(i.LAST_NAME, 'x') = nvl(t.LAST_NAME, 'x') and
nvl(i.FIRST_NAME, 'x') = nvl(t.FIRST_NAME, 'x') and
nvl(i.INIT, 'x') = nvl(t.INIT, 'x') and
nvl(i.ADDRESS1, 'x') = nvl(t.ADDRESS1, 'x') and
nvl(i.ADDRESS2, 'x') = nvl(t.ADDRESS2, 'x') and
nvl(i.CITY, 'x') = nvl(t.CITY, 'x') and
nvl(i.STATE, 'x') = nvl(t.STATE, 'x') and
nvl(i.ZIP, 'x') = nvl(t.ZIP, 'x') and
nvl(i.MARITAL_STATUS, 'x') = nvl(t.MARITAL_STATUS, 'x') and
nvl(i.GROUP_NO, 'x') = nvl(t.GROUP_NO, 'x') and
nvl(i.SUBGROUP_NO, 'x') = nvl(t.SUBGROUP_NO, 'x') and
nvl(i.COV1_PLAN, 'x') = nvl(t.COV1_PLAN, 'x') and
nvl(i.COV1_ELIG_TYPE, 'x') = nvl(t.COV1_ELIG_TYPE, 'x') and
nvl(i.COV1_EFF_DATE, 'x') = nvl(t.COV1_EFF_DATE, 'x') and
nvl(i.COV1_END_DATE, 'x') = nvl(t.COV1_END_DATE, 'x'))