how to select values by comparing two rows

Question

0.00/5 (No votes)

See more:

Hi friends,

i need to fetch distinct KEY_ACCT from the table TPOS_INTRA
criteria is
1) The account which have NUM_ACCT_TYPE both 0 and 1
2) while selecting keep in mind both KEY_ACCT,CDE_MSDW_SEC values are same

KEY_ACCT                   NUM_ACCT_TYPE CDE_MSDW_SEC
 -------------------------- ------------- ------------
 1999-09-16 16:19:09.000385             0 0000AA34H
 1999-09-16 16:19:09.000385             1 0000AA34H
 1999-10-06 14:42:43.000615             0 0000ABD09
 1999-10-06 14:42:43.000615             0 0000ACR64
 1999-10-06 14:42:43.000615             0 0000AFW65
 1999-10-06 14:42:43.000615             0 0000AHD48
 1999-10-06 14:42:43.000615             0 0000AHH17
 1999-10-06 14:42:43.000615             0 0000AWA68
 1999-10-06 14:42:43.000615             0 0000CJQ59
 1999-10-06 14:42:43.000615             0 000000360
 1999-11-15 15:41:46.000731             0 000004465
 1999-11-15 15:41:46.000731             1 000004465

my required result is
1999-09-16 16:19:09.000385
1999-11-15 15:41:46.000731

*Newly added

Query which i am using

SQL

SELECT A.KEY_ACCT, A.NUM_ACCT_TYPE, A.CDE_MSDW_SEC 
FROM DBQPEDB.TPOS_INTRA A JOIN DBQPEDB.TPOS_INTRA B 
ON A.KEY_ACCT = B.KEY_ACCT AND A.CDE_MSDW_SEC=B.CDE_MSDW_SEC WHERE 
A.NUM_ACCT_TYPE IN (0,1) FETCH FIRST 10 ROWS ONLY

see sample sql http://sqlfiddle.com/#!2/5e351f/1[^]

Posted 1-Jul-15 22:05pm

jinesh sam

Updated 3-Jul-15 1:12am

v3

Add a Solution

Comments

Herman<T>.Instance 2-Jul-15 5:51am

What have you tried?
How does your SQL looks like?

jinesh sam 2-Jul-15 6:08am

I have updated the question. please go through

deepankarbhatnagar 2-Jul-15 5:59am

please explain your query, I am not getting what you want to ask..

jinesh sam 2-Jul-15 8:42am

Num_Acct_type 0 stands for cash and 1 stands for margin
I need to get the account which have both
cash and margin
and CDE_MSDW_SEC should be same to for both key acct

jinesh sam 3-Jul-15 6:31am

please use this link http://sqlfiddle.com/#!2/5e351f

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jitesh Hirani · Answer 1 · 2015-07-02T00:37:00

Solution 1

Please try this

SQL

SELECT A.KEY_ACCT, A.NUM_ACCT_TYPE, A.CDE_MSDW_SEC
FROM DBQPEDB.TPOS_INTRA A with (NOLOCK) where A.CDE_MSDW_SEC in
(
    SELECT B.CDE_MSDW_SEC FROM DBQPEDB.TPOS_INTRA B with (NOLOCK) where B.NUM_ACCT_TYPE  = '1'
) and A.NUM_ACCT_TYPE  = '0'

Posted 2-Jul-15 0:37am

Jitesh Hirani

Comments

jinesh sam 2-Jul-15 7:58am

Hi dear,
i am using DB2 i think 'NOLOCK' keyword is not avaiable