Self Join 1

[pnad]

Hello all,

I have a table that has the following columns:

CoCode
PolNum
SecComCode
FormType
MainID
OrigName


The data in the tables is as follows:

CoCode PolNum SecComCode FormType MainID OrigName

L 1234 0 1 X
L 1234 0 VER 2

How do I perform a self-join on the table so that I get one row as follows:

CoCode PolNum SecComCode FormType OrigName
L 1234 0 VER X

The primary key on the table is MainID and data has been inserted in a way that we have 2 diff rows for the same set of CoCode, PolNum and SecComCode.

Any help is appreciated.

Thanks !

 

[SantaMufasa]

PNAD, Given your specifications, I don't see any requirements for a "self join". To produce the results you request, I can offer you this code:

select CoCode, PolNum, SecComCode, max(OrigName) OrigName
from <table_name>
group by CoCode, PolNum, SecComCode;

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Thanks SantaMufasa. The first part worked like a charm but I need the FormType also in my resultset.

 

[pnad]

Please ignore my earlier post. I was able to figure it out.

Thanks a bunch !