Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    5

    Question Unanswered: Max function in a query

    Hello,

    I have a query problem and I have no idea how to solve it.

    In the query there are 4 fields:

    1) ID 2) PageRank 3) PageRankNormalized 4) PageRankValue


    1) ID is the Primary Key

    2) In the PageRank field there are values from 1 to 10

    3) In the PageRankNormalized there is an expression PageRankNormalized: (3^[PageRank])

    4) In the PageRankValue there is another expression PageRankValue: ((3^[PageRank])*100)/ Max ( [PageRankNormalized] )


    Now when I run the query I get the following error message:

    You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function. (Error 3122)

    What I am trying to do is to calculate the PageRankValue (4) through an expression that divide ((3^[PageRank])*100) by the maximum value shown in the calculated field PageRankNormalized (3)

    ------------------SQL------------------------

    SELECT [UNIVERSITIES by name].ID, [UNIVERSITIES by name].PageRank, (3^[PageRank]) AS PageRankNormalized, ((3^[PageRank])*100)/Max([PageRankNormalized]) AS PageRankValue
    FROM [UNIVERSITIES by name];

    ---------------------------------------------


    I am stucked and I am not sure how to use this Max function in a calculated query.

    Thanks for your help !!

    FF

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    I think you have to do it on two steps

    SELECT [UNIVERSITIES by name].ID, [UNIVERSITIES by name].PageRank, (3^[PageRank]) AS PageRankNormalized, 0 AS PageRankValue
    FROM [UNIVERSITIES by name];

    Then try (not sure to run)

    Update [Universities by name] Set PageRankValue = ((3^[PageRank])*100)/Max([PageRankNormalized]) ;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you explain why you are using MAX? what is it the maximum of?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2006
    Posts
    5
    I use Max because I need to change scale. The normalized page rank values range in a scale from 1 (when page rank 0 => 3^0=1) to 59049 (when page rank 10 => 3^10=59049)

    I need to convert this values into a 1 to 100 scale by multiplying the normalized Page rank values by 100 and dividing by the max value.

    ((3^[PageRank])*100)/ Max ( [PageRankNormalized] )


    I could substitute 59049 with the expression in the denominator Max ( [PageRankNormalized] ) but I would prefer to keep it dynamic so that if I change the normalization algorytm (3^[PageRank]) I can automatically get the Max PR value and the values in a scale 1-100.

    Hope it is clear ....

    Thanks a lot FF

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    max of what, though?

    max of the entire column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2006
    Posts
    5
    yes the max value in the entire column

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ID
         , PageRank
         , 3^PageRank as PageRankNormalized
         , 3^PageRank * 100 / 
             ( select max(3^PageRank) 
                 from [UNIVERSITIES by name] )
            as PageRankValue
      from [UNIVERSITIES by name]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    One could also simplify
    3^x * 100 / 3^y
    to
    3^(x-y) * 100

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ivon
    One could also simplify
    3^x * 100 / 3^y
    to
    3^(x-y) * 100
    sure, but that's not the same as this --

    3^x * 100 / max(3^y)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by r937
    sure, but that's not the same as this --

    3^x * 100 / max(3^y)
    My y would be select max(...) etc.

    I'm pretty sure that max(3^z) equals 3^(max(z))

    3^PageRank * 100 /
    ( select max(3^PageRank)
    from [UNIVERSITIES by name] )

    would become

    100 * 3^(PageRank - ( select max(PageRank) from [UNIVERSITIES by name] ) )

    Which is about 2 characters longer, but should take less time to calculate since the ^ operator is used only once.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, good point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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