Getting the first record 2

[AgentM]

I want to select only one record from a group of similar records. How do I do that?

Let me make it more clear.
Lets say 1 husband has 1 wife or more or none.
There is a table with all the husbands with all their wives.

Now I want to run a query on that table that will give me a husband and one wife if he has one, there should not be a duplicate row for that particular husband. The column names are HusbandCol, wifecol.
Can somebody give any clues what the query should be?

Any help highly appreciated.
thank you

 

[ninfan]

One quick way is to add the following to your WHERE clause:
WHERE ROWNUM = 1

 

[AgentM]

The RowNum will work only for one particular husband_id.
What if I want to creat a view with all husbands and their wife and only one extra wife ( which can be wife 2 or 3.)?

Eg. SELECT HUSBAND_ID, WIFE_ID, EXTRA_WIFE_ID from Table where .....

The husband_ID should not be repeated in another row.

Thank you for all the help

 

[Dagon]

Best I could do for this one was:

select a.husband_id, a.wife_id, b.wife_id
from table a, table b
where a.husband_id = b.husband_id
and a.wife_id =
(select min(c.wife_id) from table c
where c.husband_id = a.husband_id)
and b.wife_id =
(select min(d.wife_id) from table d
where d.husband_id = a.husband_id
and d.wife_id <> a.wife_id)
union all
select a.husband_id, a.wife_id, 0
from table a
where a.wife_id =
(select min(b.wife_id) from table b
where b.husband_id = a.husband_id)
and not exists
(select 'x' from table c
where c.husband_id = a.husband_id
and c.wife_id <> a.wife_id)

This gives husband_id, the first wife_id and either the second wife_id or 0. I couldn't get it to return null instead of zero because it objected to the fact that null has a different datatype to wife_id, which I had as numeric. Presumably null is char.

I can't see how a husband can have zero wives because, in that case, he wouldn't be a husband at all !

 

[AgentM]

Thank you very much.
I tried running the big query but I got an error message
&quot;Expression must have same datatype as corresponding expression&quot;
And it stops at the first line at b.wife_id

Also the id's are characters.
Any help is higly appreciated.

thank you

 

[Dagon]

Is your wife_id a character datatype ? You could just re-cast it to:

select a.husband_id, a.wife_id, b.wife_id
from table a, table b
where a.husband_id = b.husband_id
and a.wife_id =
(select min(c.wife_id) from table c
where c.husband_id = a.husband_id)
and b.wife_id =
(select min(d.wife_id) from table d
where d.husband_id = a.husband_id
and d.wife_id <> a.wife_id)
union all
select a.husband_id, a.wife_id, '0'
from table a
where a.wife_id =
(select min(b.wife_id) from table b
where b.husband_id = a.husband_id)
and not exists
(select 'x' from table c
where c.husband_id = a.husband_id
and c.wife_id <> a.wife_id)

 

[AgentM]

I tried using to_number to convert the id's but still it gives me the same error.

&quot;Expression must have same datatype as corresponding expression&quot;
And it stops at the first line at b.wife_id

Both the wife_id and husband_id are character datatypes.
Please let me know what the query will be ?

 

[Dagon]

If your Id's are of a character datatype, then you should need this:

select a.husband_id, a.wife_id, b.wife_id
from table a, table b
where a.husband_id = b.husband_id
and a.wife_id =
(select min(c.wife_id) from table c
where c.husband_id = a.husband_id)
and b.wife_id =
(select min(d.wife_id) from table d
where d.husband_id = a.husband_id
and d.wife_id <> a.wife_id)
union all
select a.husband_id, a.wife_id, '0'
from table a
where a.wife_id =
(select min(b.wife_id) from table b
where b.husband_id = a.husband_id)
and not exists
(select 'x' from table c
where c.husband_id = a.husband_id
and c.wife_id <> a.wife_id)

You don't need the to_number as the second query has '0' (character) rather than 0 (number). You should also be able to use null instead of '0'.

 

[Guest_imported]

There is a simpler solution. Assume you have this table (h for husband, hu for husband_id, wi for wife_id):

create table h (
hu number,
wi number);

insert into h values (1,1);

insert into h values (2,1);
insert into h values (2,2);

insert into h values (3,1);
insert into h values (3,2);
insert into h values (3,3);

insert into h values (4,null);
insert into h values (5,null);

This says: husband 1 has 1 wive
husband 2 has 2 wives
husband 3 has 3 wives
husband 4 has 0 wife (null id)
husband 5 has 0 wife (null id)

I belive this query will return what you want (it pick the smallest wife_id for each husband)

select h.hu, i.wi
from (select hu, min(wi) wi from h group by hu) i,
(select distinct hu from h) h
where h.hu = i.hu(+);


HU WI
---------- ----------
1 1
2 1
3 1
4 <== no wife
5 <== no wife

 

[carp]

I haven't tested this one, but I think it ought to work:

SELECT a.husband, b.wife
FROM my_table a,
(SELECT wife
FROM my_table c
WHERE c.husband = a.husband
AND rownum = 1) b;

Of course, with a correlated subquery, it may not be the most efficient approach