Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: To find number of records for the particular combination , irrespective of the order

    Hello All,

    Can anyone please provide me a query to get this resultset

    a1 a2 count
    1 2 3
    2 3 1


    data in table
    a1 a2
    1 2
    1 2
    2 3
    2 1

    Basically , i want to check number of records for the particular combination , irrespective of the order of the column.

    simply group by treats 1,2 and 2,1 as distinct.

    thanx alot for your help in advance
    --Pooja

  2. #2
    Join Date
    Aug 2004
    Posts
    0

    Number of records

    Query can be used is:

    Select a1, a2, count(*) from
    (Select a1, a2 from table where a <= b
    union all
    Select a2, a1 from table where b < a) as abc
    group by a1, a2

    Basically, i have picked the rows in which first column value is greater than second value and then i picked the rest of rows by changing first and second columns. Now the order of columns is same and i have simply grouped the rows on the basis of these two columns.

    Parveen
    www.daffodildb.com

  3. #3
    Join Date
    Aug 2004
    Posts
    42
    Or...

    select
    case when a1 < a2 then a1 else a2 end
    ,case when a1 < a2 then a2 else a1 end
    ,count(*)
    from ...
    group by
    case when a1 < a2 then a1 else a2 end
    ,case when a1 < a2 then a2 else a1 end

Posting Permissions

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