Solved

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

Posted on 2014-03-22
4
643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

632 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