Solved

SQL syntax to select latest instance of a date field (using MYSQL on Linux)

Posted on 2014-03-22
4
637 Views
Last Modified: 2014-03-22
Hi,

I would like to request assistance with an sql statement wherein I would like to update an SSL certificates table with the latest instance of the expiration date of a certificate, in a situation where there are multiple versions of the same certificate installed in a particular network device.  A sample of my existing SQL table looks like this


Table: SSL_CERT_PER_DEVICE

PRI_KEY   CN_Name     Expiry             Issuer               Device             LatestCNExpiry
1         a.b.c.com    12-March-2013        issuer.com       Device-a         null
2         b.def.com    13-March-2013        issuer.com       Device-b         null
3         a.b.c.com    14-March-2015        issuer.com       Device-a         null
4         a.b.c.com    15-March-2009        issuer.com       Device-a         null
5         a.b.c.com    23-March-2013        issuer.com       Device-d         null

Open in new window



In this example, "Device-a" has 3 instances of a.b.c.com issued by issuer.com,one that expired in 2009, then 2013, then 2015.  So I want the column LatestCNExpiry to show that the latest version of this cert is 15-March-2015.  Expiry and LatestCNEpxiry would be normal mysql DATA fields, so i can do date computations on them.   The rest are just strings.  In my case, a distinct record (Unique index) would be CN_Name +issuer + Device.   And within the unique record of CN_Name+issuer+device there could be be several different "Expiry" records. The output I am expecting would look like this:


PRI_KEY    CN_Name     Expiry             Issuer               Device                 LatestCNExpiry
1         a.b.c.com    12-March-2013        issuer.com       Device-a           14-March-2015
2         b.def.com    13-March-2013        issuer.com       Device-b            13-March-2013
3         a.b.c.com    14-March-2015        issuer.com       Device-a            14-March-2015
4         a.b.c.com    15-March-2009        issuer.com       Device-a            14-March-2015
5         a.b.c.com    23-March-2013        issuer.com       Device-d            23-March-2013

Open in new window


So I would want to convert the following "English"  statement to an "SQL" statement but not sure how to set it up:    UPDATE SSL_CERT_PER_DEVICE SET LatestCNExpiry= maximum (Expiry) where CN_NAME + Issuer + device = current record's CN_NAME + Issuer + Device

Thanks very much.
0
Comment
Question by:rleyba828
4 Comments
 
LVL 5

Assisted Solution

by:jayakrishnabh
jayakrishnabh earned 40 total points
ID: 39947431
;WITH CTE AS(
   SELECT CN_Name, Issuer, Device ,Expiry, LatestCNExpiry,
       RN1 = max(Expiry) OVER(PARTITION BY CN_Name, Issuer, Device )      
       
      FROM SSL_CERT_PER_DEVICE
)
update CTE
set LatestCNExpiry=RN1
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 40 total points
ID: 39947687
The 'normal' MySQL date format would be 2013-03-12, not 12-March-2013.  You can use 12-March-2013 as a display format but I have never seen it work in MySQL Date functions or comparisons.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 420 total points
ID: 39948172
Regarding suggested use of: MAX(...) OVER(PARTITION BY ... ORDER BY ...)
I don't believe any MySQL version provides this (or any) analytic function as at March 2014

Try this:
    CREATE TABLE SSL_CERT_PER_DEVICE
    	(`PRI_KEY` int, `CN_Name` varchar(9), `Expiry` datetime, `Issuer` varchar(10), `Device` varchar(8), `LatestCNExpiry` datetime)
    ;
    	
    INSERT INTO SSL_CERT_PER_DEVICE
    	(`PRI_KEY`, `CN_Name`, `Expiry`, `Issuer`, `Device`, `LatestCNExpiry`)
    VALUES
    	(1, 'a.b.c.com', '2013-03-12 00:00:00', 'issuer.com', 'Device-a', '2015-03-14 00:00:00'),
    	(2, 'b.def.com', '2013-03-13 00:00:00', 'issuer.com', 'Device-b', '2013-03-13 00:00:00'),
    	(3, 'a.b.c.com', '2015-03-14 00:00:00', 'issuer.com', 'Device-a', '2015-03-14 00:00:00'),
    	(4, 'a.b.c.com', '2009-03-15 00:00:00', 'issuer.com', 'Device-a', '2015-03-14 00:00:00'),
    	(5, 'a.b.c.com', '2013-03-23 00:00:00', 'issuer.com', 'Device-d', '2013-03-23 00:00:00')
    ;
    
    
UPDATE SSL_CERT_PER_DEVICE AS dev
INNER JOIN (
            SELECT `CN_Name`, `Issuer`, `Device`, max(`expiry`) AS max_expiry
            FROM SSL_CERT_PER_DEVICE
            GROUP BY `CN_Name`, `Issuer`, `Device`
           ) AS exp ON  dev.CN_Name = exp.CN_Name
                    AND dev.Issuer  = exp.Issuer
                    AND dev.Device  = exp.Device
SET
   dev.LatestCNExpiry = exp.max_expiry
    	
    

**Query 1**:

    
    SELECT
    *
    FROM SSL_CERT_PER_DEVICE

**[Results][2]**:
    
    | PRI_KEY |   CN_NAME |                       EXPIRY |     ISSUER |   DEVICE |               LATESTCNEXPIRY |
    |---------|-----------|------------------------------|------------|----------|------------------------------|
    |       1 | a.b.c.com | March, 12 2013 00:00:00+0000 | issuer.com | Device-a | March, 14 2015 00:00:00+0000 |
    |       2 | b.def.com | March, 13 2013 00:00:00+0000 | issuer.com | Device-b | March, 13 2013 00:00:00+0000 |
    |       3 | a.b.c.com | March, 14 2015 00:00:00+0000 | issuer.com | Device-a | March, 14 2015 00:00:00+0000 |
    |       4 | a.b.c.com | March, 15 2009 00:00:00+0000 | issuer.com | Device-a | March, 14 2015 00:00:00+0000 |
    |       5 | a.b.c.com | March, 23 2013 00:00:00+0000 | issuer.com | Device-d | March, 23 2013 00:00:00+0000 |



  [1]: http://sqlfiddle.com/#!9/99b55/1

  [2]: http://sqlfiddle.com/#!9/99b55/1/0

Open in new window

0
 

Author Comment

by:rleyba828
ID: 39948269
Hi PortletPaul,   thanks very much.  It was your solution that worked.  

For Dave,   I just eyeballed the date format because I composed the question when I was at home and not at the office, but yes, you are correct about the format.

For  jayakrishnabh, your solution did not work for mysql., but thanks for trying to come up with a plan.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question