Comparison of two rows in a table using SQL ?

[santoshsri]

Hi,

Can anybody tell me how to compare two rows in a single tablle using SQL ?

[Pragathiarun]

Hi,

Can anybody tell me how to compare two rows in a single tablle using SQL ?

Try Self joins

eg: Select * from Table1 A, Table1 B
where (conditions )

[santoshsri]

Hi ,

Below is a sample data of the table.

IBPSUB IBPQTN CLIENT_INFORMATION CLASS ASSESTS
18783 1 Echo Test 1 - Client A 730 100000
18783 2 Echo Test 1 - Client A 730 120000
18784 1 Echo Test 1 - Client B 853 130000
18785 1 Echo Test 1 - Client C 853 100000
18785 81 Echo Test 1 - Client C 854 100000


Primary keys : IBPSUB and IBPQTN

My problem is : Whenever a Record having a particular IBPSUB has a IBPQTN number = 80 + IBPQTN , then I will have display the only record having greater IBPQTN value ..


My expected output from above table should be :

IBPSUB IBPQTN CLIENT_INFORMATION CLASS ASSESTS
18783 1 Echo Test 1 - Client A 730 100000
18783 2 Echo Test 1 - Client A 730 120000
18784 1 Echo Test 1 - Client B 853 130000
18785 81 Echo Test 1 - Client C 854 100000

IBPSUB 18785 has IBPQTN #1 and 81 ... since this IBPSUB had 1 and 81 in problem so the result should display only 81 not 1.

That's my problem ..

Please help me

Thanks

[Mo Nguyen]

What you need is a query with correlated subquery using Max:

Select *
from table1 a
where IBPQTN =
(select max(IBPQTN)
from table1 b
where a.IBPSUB = b.IBPSUB)