Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: Find max entry in table

    I would like to select the highest id in a table for each computer record.

    example data:
    ID Computer name
    24 computer1
    23 computer1
    22 computer2
    24 computer3

    The max id is not going to be the same for every computer.

    When the script below is run, I receive the highest number only. All IDs with 24 will be listed. I need all the computer names and the highest ID they are associated with. In the case above, 24 computer1 should be on the list and 23 computer1 should not.

    Thanks,

    Script....

    Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
    From servers s Join processors p
    On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
    where s.id_snapshot = (Select MAX (id_snapshot)
    From servers)

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
    From servers s Join processors p
    On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
    inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    mpereziii, your query should work too ... Do you have any issues?

    Originally posted by rdjabarov
    Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
    From servers s Join processors p
    On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
    inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    With the original query mpereziii was getting only rows where id_snapshot was equal to the maximum value in the table.

  5. #5
    Join Date
    Dec 2003
    Posts
    11
    Thanks for the response.
    I adjusted my script with your suggestion. I received too many rows. 424,000 rows and it searched for 18sec.

    The query returned every id_snapshot for every server_name (computer name).

    Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
    From servers s Join processors p
    On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
    inner join (Select sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid

  6. #6
    Join Date
    Dec 2003
    Posts
    17
    How about this:

    Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
    From servers s Join processors p
    On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
    inner join (Select server_name, sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid and s.server_name = maxsid.server_name

  7. #7
    Join Date
    Dec 2003
    Posts
    11
    Thank you sjp. The query now works very well. I really appreciate everyones input. Thank you.

Posting Permissions

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