Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: distinct values - why won't this work?

    I have a table with fields UNIT,LATITIUDE,LONGITUDE,and TIME

    data is like so...

    10 41 95 1100
    10 41 94 1030
    16 40 95 1025
    10 41 94 1015
    15 41 93 1007
    16 40 94 0955


    I want to select the last entry (most current) in the table for each UNIT.

    I tried SELECT DISTINCT UNIT,LATITUDE,LONGITUDE,TIME but I am only getting one UNIT's data, not each UNIT's most current data.

    HELP! thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, time) AS (
    VALUES
     (10,41,95,1100)
    ,(10,41,94,1030)
    ,(16,40,95,1025)
    ,(10,41,94,1015)
    ,(15,41,93,1007)
    ,(16,40,94,0955)
    )
    /* End of test data */
    
    SELECT unit, latitude, longitude, time
      FROM TestData A
     WHERE time
           = (SELECT MAX(time)
                FROM TestData B
               WHERE b.unit = a.unit
             )
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   TIME       
    ----------- ----------- ----------- -----------
             10          41          95        1100
             16          40          95        1025
             15          41          93        1007
    
      3 record(s) selected.
    
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, time) AS (
    VALUES
     (10,41,95,1100)
    ,(10,41,94,1030)
    ,(16,40,95,1025)
    ,(10,41,94,1015)
    ,(15,41,93,1007)
    ,(16,40,94,0955)
    )
    /* End of test data */
    
    SELECT unit, latitude, longitude, time
      FROM (SELECT A.*
                 , ROWNUMBER() OVER(PARTITION BY unit
                                        ORDER BY time DESC) rn
              FROM TestData A
           ) S
     WHERE rn = 1
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   TIME       
    ----------- ----------- ----------- -----------
             10          41          95        1100
             15          41          93        1007
             16          40          95        1025
    
      3 record(s) selected.

  3. #3
    Join Date
    Sep 2008
    Posts
    34
    Ok, now I also have a DATE field in the table. That SQL statement won't work anymore because the MAX(TIME) needs to be selected only from the MAX(DATE). any help would be greatly appreciated.

  4. #4
    Join Date
    Sep 2008
    Posts
    7
    You can modify one of the queries given above to

    Code:
    WITH
     TestData(unit, latitude, longitude, time, date) AS (
    VALUES
     (10,41,95,1100,'26/07/2008')
    ,(10,41,94,1030,'26/07/2008')
    ,(16,40,95,1025,'27/07/2008')
    ,(10,41,94,1015,'27/07/2008')
    ,(15,41,93,1007,'28/07/2008')
    ,(16,40,94,0955,'28/07/2008')
    )
    /* End of test data */
    
    SELECT unit, latitude, longitude, time, date
      FROM (SELECT A.*
                 , ROWNUMBER() OVER(PARTITION BY unit
                                        ORDER BY date desc, time DESC) rn
              FROM TestData A
           ) S
     WHERE rn = 1
    ;
    just look at the data in bold..... the data will now be sorted first by date descending and then by time in descending order..... this would satisfy your requirement


    ResultSet for your reference
    10 41 94 1015 27/07/2008
    15 41 93 1007 28/07/2008
    16 40 94 955 28/07/2008
    Last edited by v3nom; 10-08-08 at 01:23.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are three ways.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, date, time) AS (
    VALUES
     (10,41,95,20081007,1100)
    ,(10,41,94,20081008,1030)
    ,(16,40,95,20081007,1025)
    ,(10,41,94,20081008,1015)
    ,(15,41,93,20081007,1007)
    ,(16,40,94,20081008,0955)
    )
    /* End of test data */
    
    SELECT unit, latitude, longitude, date, time
      FROM (SELECT A.*
                 , ROWNUMBER() OVER(PARTITION BY unit
                                        ORDER BY date DESC, time DESC) rn
              FROM TestData A
           ) S
     WHERE rn = 1
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   DATE        TIME       
    ----------- ----------- ----------- ----------- -----------
             10          41          94    20081008        1030
             15          41          93    20081007        1007
             16          40          94    20081008         955
    
      3 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, date, time) AS (
    VALUES
     (10,41,95,20081007,1100)
    ,(10,41,94,20081008,1030)
    ,(16,40,95,20081007,1025)
    ,(10,41,94,20081008,1015)
    ,(15,41,93,20081007,1007)
    ,(16,40,94,20081008,0955)
    )
    /* End of test data */
    
    SELECT a.*
      FROM TestData A
     INNER JOIN
           (SELECT unit
                 , MAX(date) AS max_date
              FROM TestData B
             GROUP BY unit
           ) MD
       ON  md.unit     = a.unit
       AND md.max_date = a.date
     WHERE a.time
           = (SELECT MAX(time)
                FROM TestData C
               WHERE c.unit = a.unit
                 AND c.date = md.max_date
             )
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   DATE        TIME       
    ----------- ----------- ----------- ----------- -----------
             10          41          94    20081008        1030
             15          41          93    20081007        1007
             16          40          94    20081008         955
    
      3 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, date, time) AS (
    VALUES
     (10,41,95,20081007,1100)
    ,(10,41,94,20081008,1030)
    ,(16,40,95,20081007,1025)
    ,(10,41,94,20081008,1015)
    ,(15,41,93,20081007,1007)
    ,(16,40,94,20081008,0955)
    )
    /* End of test data */
    
    SELECT a.*
      FROM TestData A
     INNER JOIN
           LATERAL
           (SELECT date, time
              FROM TestData B
             WHERE b.unit = a.unit
             ORDER BY
                   date DESC, time DESC
             FETCH FIRST 1 ROW ONLY
           ) B
       ON  b.date = a.date
       AND b.time = a.time
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   DATE        TIME       
    ----------- ----------- ----------- ----------- -----------
             10          41          94    20081008        1030
             15          41          93    20081007        1007
             16          40          94    20081008         955
    
      3 record(s) selected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TestData(unit, latitude, longitude, date, time) AS (
    VALUES
     (10,41,95,20081007,1100)
    ,(10,41,94,20081008,1030)
    ,(16,40,95,20081007,1025)
    ,(10,41,94,20081008,1015)
    ,(15,41,93,20081007,1007)
    ,(16,40,94,20081008,0955)
    )
    /* End of test data */
    
    SELECT a.*
      FROM TestData A
     WHERE (date, time) IN /* or "= ANY" */
           (SELECT date, time
              FROM TestData B
             WHERE b.unit = a.unit
             ORDER BY
                   date DESC, time DESC
             FETCH FIRST 1 ROW ONLY
           )
    ;
    ------------------------------------------------------------------------------
    
    UNIT        LATITUDE    LONGITUDE   DATE        TIME       
    ----------- ----------- ----------- ----------- -----------
             10          41          94    20081008        1030
             15          41          93    20081007        1007
             16          40          94    20081008         955
    
      3 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (Deleted, because of duplicated post.)
    Last edited by tonkuma; 10-08-08 at 10:41.

  8. #8
    Join Date
    Sep 2008
    Posts
    34
    The first example using JOIN worked perfectly. thank you very much for your help!!!!

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yet another idea: combine your DATE and TIME values into a TIMESTAMP and apply MAX() to that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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