Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Cool Unanswered: Complex query problem


    I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

    Tab1.efID Tab1.VID Tab2.efID Tab2.VID
    $00046342 7 $00046342 8
    $00046342 7 $00046342 19
    $00046342 18 $00046342 19

    I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

    The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

    SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    See your other thread asking this question.


Posting Permissions

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