Solved

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

Posted on 2014-03-22
4
639 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
online  environment for testing sql queries 5 33
Finding Where Clause Value in SQL Views and SP 21 43
SQL Syntax 6 43
Windows 10 linux VM 22 51
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

697 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