Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Unanswered: Big help is needed...

    I have a table that reads like the following:

    Code:
    --ST-- | --DOK-- | --Occurence--
           1 |          1 |                 6
           2 |          2 |                 5
           3 |          1 |                 3
           3 |          2 |                 3
           3 |          3 |                 1
    It means the following:
    - In ST (aka Item) 1, its depth-of-knowledge (DOK) contains only 1's, and it has occured 6 times (ie. someone rated [Item 1] six times with 1)
    - In Item 2, someone rated it five times with 2
    - In Item 3, someone rated it altogether seven times, with values from 1 through 3. The distribution is stated in the Occurence column.

    I want to find the statistical MODE (most frequently appeared) value of the DOK for each ST (aka Item). In case of a tie, I want to choose the max of all the ties (In Item 3, it will be '2' that gets chosen instead of '1')

    I am unable to write up my SELECT statement after trying and trying for 2 days... can someone with extensive SQL experiences help me with this statistics MODE problem?

    Million thanks in advance...

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    if you used a view for the query which generated that output; you could do this;
    Code:
    select max(st)
    from (select st,count(*) from VIEW_OF_ST group by st order by st)
    where rownum<2
    Is this what you want? It should return 3 if used on that set of data above.

  3. #3
    Join Date
    Feb 2003
    Posts
    15
    Not exactly... I would like the result set to be of the format:
    Code:
    --ST--|--DOK--
          1 |       1
          2 |       2
          3 |       2       //For ST=3, its maximum most-frequently-appeared DOK is 2
    Still struggling... any help is very highly appreciated...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:

    select st, max(dok)
    from t
    where (st, occurence) in
    (
    select st, max(occurence) max_occurence from t
    group by st
    )
    group by st;

  5. #5
    Join Date
    Feb 2003
    Posts
    15
    Tony,

    For some reason it tells me that there's a syntax error using the notation "WHERE (ST, Occurence) IN"

    I looked at SQL Server Books Online and can't quite find much about this "(...)" notation.

    Is this notation only supported in some DB? I am using SQL Server 2000.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Ah, I am using Oracle - maybe this syntax is non-standard then, though I had never realised that. What about "in-line views"?

    select t.st, max(t.dok)
    from t,
    (
    select st, max(occurence) max_occurence from t
    group by st
    ) v
    where t.st = v.st
    and t.occurence = v.max_occurence
    group by t.st;

  7. #7
    Join Date
    Feb 2003
    Posts
    15
    Beautiful!!!!!!!!! You are a heck of a programmer!!!!! 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
  •