Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Question Unanswered: Order By / Grouping / Where

    Good afternoon
    I am currently writing an asset database that shows computer history and asset information.
    However I am struggling with one sql statement,
    I need to be able to select * from assets where the domain name = $domain
    have the computernames grouped so as they only show once, but it shows me the one with the highest revision number

    for example if the data was:
    computer name | domain | revision number
    simon1 | alpha | 1
    simon1 | alpha | 2
    simon1 | alpha | 3
    simon1 | george | 1

    I want my results to be: simon1 on domain alpha revision 3

    Thank you for any help.

  2. #2
    Join Date
    Sep 2012
    Posts
    2
    I have this:
    SELECT * FROM assets INNER JOIN (SELECT MAX(Revision) AS Revision FROM assets group by Computer_ServiceTag) ids ON assets.Revision = ids.Revision
    But where can I add WHERE Domain_Name = $domain

    Thanks

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The where clause is after the JOIN clause.
    SELECT * FROM assets INNER JOIN (SELECT MAX(Revision) AS Revision FROM assets group by Computer_ServiceTag) ids ON assets.Revision = ids.Revision
    WHERE Domain_Name = $domain

    Im guessing you are using PHP

    $strSQL = "SELECT * FROM assets INNER JOIN (SELECT MAX(Revision) AS Revision FROM assets group by Computer_ServiceTag) ids ON assets.Revision = ids.Revision
    WHERE Domain_Name = '".$domain."';";

    the ' delimits the string litteral values for the SQL engine
    the " delimits the string value in PHP
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hole on, mon, we got to back up a bit

    start with the subquery --
    Code:
    SELECT MAX(Revision) AS Revision 
      FROM assets 
    group by Computer_ServiceTag
    this will produce one row for every different Computer_ServiceTag

    however, the only column being returned is the max of the revision

    so, using the (painfully sparse) sample data in post #1, we have these results from the subquery --
    Code:
    3
    1
    now we are going to join every row in the assets table to these maxes based on the max revision number

    so, using the (painfully sparse) sample data in post #1, we get the desired results, but this is only a coincidence

    suppose there were a number of assets where the max revision number is 1 -- do you realize you will get painfully many cross join results?

    the subquery must be altered to output the Computer_ServiceTag, and the join to the subquery must be altered to join on this column as well
    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
  •