Results 1 to 8 of 8

Thread: query question

  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Unanswered: query question

    I have a table with four records and two columns that looks like this

    (hmm somehow i cannot get the values aligned but, <tab> does not work, anyhow, the values 2 1 4 and 3 should be in column TRNY)


    TRNX TRNY
    1 2
    2 1
    3 4
    4 3

    Where TRNX and TRNY are my column names.
    to me record 1 and 2 are identical and record 3 and 4 too so i need a query on this table that gives me the following output

    TRNX TRNY
    1 2
    3 4

    (or of course:
    TRNX TRNY
    2 1
    4 3
    )

    Is this possible ??
    Last edited by Kabuki_jo; 07-16-04 at 11:45.
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Mar 2004
    Posts
    27
    If the two records are really identical you can use 'disctinct'

    select distinct (TRNX) from ....
    Atha
    20175337

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select name, (select TOP 1 name from a where name > a1.name AND name MOD 2 = 0)
    from a a1
    where (NOT name mod 2 = 0)
    Attached Thumbnails Attached Thumbnails image.JPG  
    Last edited by r123456; 07-16-04 at 12:15.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Oct 2003
    Posts
    61
    Quote Originally Posted by r123456
    Select name, (select TOP 1 name from a where name > a1.name AND name MOD 2 = 0)
    from a a1
    where (NOT name mod 2 = 0)
    this is not it. My table already has two columns. Column TRNX and column TRNY. In this table there are four records. To make it a little clearer i will change the values (please ignore the underscores, i have used them since <tab> and spaces do not work

    TRNX___TRNY
    a______b
    b______a
    c______d
    d______c


    i want the query on this table to give only two records of output since to me the first and the second record are equal and the third and the fourth too.
    (i know they are not but as far as i am concerned they are!)

    therefore want i want to see is this as output of my query:

    TRNX__TRNY
    a_____b
    c_____d

    anyone??
    Nobody dies a virgin because life screws us all!

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    why do you have two fields with the same data, and ultimately to not see it?
    BTW to be able to use spaces, use the [ CODE ] [ /CODE ] tags. You may be able to use
    HTML Code:
    &nbsp;
    but I am not sure...
    Last edited by rguy84; 07-16-04 at 12:57.
    Ryan
    My Blog

  6. #6
    Join Date
    Oct 2003
    Posts
    61
    Quote Originally Posted by rguy84
    why do you have two fields with the same data, and ultimately to not see it?
    BTW to be able to use spaces, use the [ CODE ] [ /CODE ] tags. You may be able to use
    HTML Code:
    &nbsp;
    but I am not sure...
    I am matching trades that belong together. however after the matching proces i will have two records for every match

    record one has trnx = a and trny = b
    record two has trnx = b and trny = a

    to me one of the two records is a duplicate in which i am not interested because the first record already shows me which trades tie together.
    Nobody dies a virgin because life screws us all!

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    So column trnx has A for record one, you want to take that, hop to field trny to see if any record in trny = a. if so hide it? Now will there ever be 2 a's under trny? I used to know how to do it in Java...but yeah
    Ryan
    My Blog

  8. #8
    Join Date
    Oct 2003
    Posts
    61
    Quote Originally Posted by rguy84
    So column trnx has A for record one, you want to take that, hop to field trny to see if any record in trny = a. if so hide it? Now will there ever be 2 a's under trny? I used to know how to do it in Java...but yeah
    yep that's what i want .There might be two a's under TRNY but that is a problem with which i can cope quite easily and which is not relevant here. but since you don't know the solution this is not helping much
    Nobody dies a virgin because life screws us all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •