Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Unanswered: Need basic SQL query help

    Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

    The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
    The two fields I need to use to identify unique records are:
    serial_IP and site_id

    In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.


    Last edited by noclue; 11-17-05 at 23:29. Reason: Additional information added

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    As those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

    SELECT DISTINCT serial_IP, site_id FROM your_table;

    However, regarding your next request (selecting records with the latest date), there's need to use a subquery:
    SELECT DISTINCT t.serial_IP, t.site_id
    FROM your_table t
    WHERE t.date_column = (SELECT max(t1.date_column)
                           FROM your_table t1
                           WHERE t1.serial_IP = t.serial_IP
                             AND t1.site_id = t.site_id
    The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Posting Permissions

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