Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Question Unanswered: max(tag) for pair of matched rows (was "Need help on query")

    I've got a table of transactions which are linked up in pairs based on the column 'Ref'. 'F's are the identifiers of each transaction while 'S's points to the 'F' of its matching pair. I need to select all the transactions with the larger 'Tag' for each pair, can someone point me in the right direction?

    Tag Ref Type
    --- --- -----
    1 200 F
    1 201 S
    2 201 F
    2 200 S
    3 202 F
    3 203 S
    4 203 F
    4 202 S
    5 204 F
    5 205 S
    6 205 F
    6 204 S

  2. #2
    Join Date
    Feb 2005
    Posts
    78
    Is this what you are after?

    select t.tag, t.ref, t.type
    from t
    join (select max(tag)as tag, ref from t
    group by ref) b
    on t.tag = b.tag
    and t.ref = b.ref

    giving you the result set

    6 205 F
    6 204 S
    4 203 F
    4 202 S
    2 201 F
    2 200 S

  3. #3
    Join Date
    Feb 2005
    Posts
    3
    Yes it is, thanks!

Posting Permissions

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