Solved

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

Posted on 2014-03-22
4
627 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
Comment Utility
;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 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 40 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now