Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-03-22
4
Medium Priority
?
651 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 160 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 160 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 1680 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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

722 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