group by query

[#1]

Hi Team,

LC	PC
AGRA	28
AHMEDABAD	38
AHMEDNAGAR	41
AHMEDNAGAR	41
AHMEDNAGAR	42
AIZWAL	79
AJC BOSE RD	70
AMRAVATI	44
AMRELI	35
AMRELI	36

Above is the sample data. I have to find out LCs appearing in different PC eg in above sample data Ahmednagar and Amreli are two LCs where PCs are different.

I can use Peek function for this, eg.

Load LC,PC
Resident Sample_Data
Where LC = Peek(LC) And PC <> Peek(PC);

But there is one problem with this is that i will have only

AHMEDNAGAR	42
AMRELI	36

means where LC is same and PC is different.

But for comparison, I want the output as below:

AHMEDNAGAR	41
AHMEDNAGAR	42
AMRELI	35
AMRELI	36

means all distinct records where PCs are different.

Any suggestion!!

I know the use of Where Not Exist(LC) but can i use eg.

Where Not Exist (LC) And Not Exist (PC)
OR Not Exist (LC.PC)

[#2]

I was thinking you could combine LC and PC to create a unique key and just not load the one that are found more than once.

tmp:

LOAD

LC&' '&PC as ID,

LC,

PC

Resident Sample_Data;

myTable:

noconcatenate load

ID,

LC,

PC

resident tmp where ID <> peek(ID);

drop table tmp;

Good luck!

BR,

Ida

[johnw]

How about this?

LOAD LC
FROM data source
WHERE LC = peek(LC)
AND PC <> peek(PC)
ORDER BY LC, PC
;
LEFT JOIN
LOAD LC, PC
FROM data source
;