Quantcast

MySQL - Max date query HELP!!

Bubblin

Well-known Member
Right, simple one, but I'm stumped

I have a table

Name Date Status
A 01/04/2016 Good
A 04/04/2016 Bad
A 07/04/2016 Awful

I know how to get max date, and staus from that in MySQL, but I also want for each line the previous date and previous status i.e

Name Date Status Previous Date Previous Status
A 01/04/2016 Good {null} {null}
A 04/04/2016 Bad 01/04/2016 Good
A 07/04/2016 Awful 04/04/2016 Bad

How would you do that in a view (not a procedure)

Any help would be fantastic?
 

markie g

Distinguished Member
What is Previous Date and Previous Status? Where do they come from - another table or is it another column in the same table?

To me it comes across as you are recording previous dates and statuses within your application. To me it make sense to have a separate table (if you're not doing it already) that stores a log of all the previous dates and statuses (i'm guessing there could be several).

If it's all in one table then a view of:

CREATE VIEW [NAME OF VIEW HERE] AS
SELECT `Name`,`Date`, `Status`, `Previous Date`, `Previous Status`
FROM `NAME OF TABLE HERE`

You'd just need to add your view name and name of the table. However, if it's not one table you'll need to do a JOIN.

It would be better if we could see the actual structure of your table(s) so we ensure we are giving the exact name of the columns/tables.
 
Last edited:
This works in SQL Server so should work in MySQL if it supports Window Functions and CTEs

Code:
;WITH data  (Name,Date,Status,LastDate,LastStatus,RowNum)
AS
(
    SELECT
        a.[Name]
        ,a.[Date]
        ,a.[Status]
        ,b.[Date]
        ,b.[Status]
    ,ROW_NUMBER() OVER (PARTITION BY a.[Name],b.[Date] ORDER BY a.[Date])
    FROM dbo.TestTable a
    LEFT JOIN dbo.TestTable b ON a.[Name] = b.[Name] and b.[Date] < a.[Date]
)

SELECT
Data.[Name],Data.[Date],Data.[Status],Data.LastDate,Data.LastStatus
FROM
    data
WHERE
    RowNum = 1
The CTE contains all records in the table and for each row all rows for the same name which occurred before the data of that row.

ROW_NUMBER is then used to give each previous record a number sorted decended so the previous record will be 1 and subsequent previous records will have a higher number based on their date. The outer WHERE clause can choose to only return the first previous record with RowNum = 1.
 
Thanks I will have a look at this in the morning, would this work inside a view?
Yep should do,

Here's another solution that doesn't use Window Functions

Code:
;WITH cte (Name,Date,Status,LastDate)
AS
(
SELECT
a.Name,a.date,a.Status,MAX(b.date)
FROM testtable a
LEFT JOIN testtable b on a.Name=b.Name and b.Date<a.Date
GROUP BY a.name,a.date,a.Status
)

SELECT a.Name,a.Date,a.Status, b.Date AS LastDate, b.Status AS LastStatus
FROM cte a
LEFT JOIN cte b ON a.Name=b.Name AND b.Date = a.LastDate
 

Bubblin

Well-known Member
What is Previous Date and Previous Status? Where do they come from - another table or is it another column in the same table?

To me it comes across as you are recording previous dates and statuses within your application. To me it make sense to have a separate table (if you're not doing it already) that stores a log of all the previous dates and statuses (i'm guessing there could be several).

If it's all in one table then a view of:

CREATE VIEW [NAME OF VIEW HERE] AS
SELECT `Name`,`Date`, `Status`, `Previous Date`, `Previous Status`
FROM `NAME OF TABLE HERE`

You'd just need to add your view name and name of the table. However, if it's not one table you'll need to do a JOIN.

It would be better if we could see the actual structure of your table(s) so we ensure we are giving the exact name of the columns/tables.

The data I have is in the first table where I'm recording status changes, site name and date, and I want to look at dwell times between status changes, so I have 3 columns, I want to create the 5 column view using the 3 column table, and bring back the line of data and its previous status/date. The example above is the simplified view, and treat the first table as the only source of data for this example.

Total data set equates to a few hundred thousand lines, of which I'm bringing the data into a website, using asp.net to interrogate dwell.
 
Last edited:

Bubblin

Well-known Member
Yep should do,

Here's another solution that doesn't use Window Functions

Code:
;WITH cte (Name,Date,Status,LastDate)
AS
(
SELECT
a.Name,a.date,a.Status,MAX(b.date)
FROM testtable a
LEFT JOIN testtable b on a.Name=b.Name and b.Date<a.Date
GROUP BY a.name,a.date,a.Status
)

SELECT a.Name,a.Date,a.Status, b.Date AS LastDate, b.Status AS LastStatus
FROM cte a
LEFT JOIN cte b ON a.Name=b.Name AND b.Date = a.LastDate
Thanks, here's me now googling cte functions
 
Thanks, here's me now googling cte functions
In this example, I'm just using it as an inlne view so it's functionally the same as a derived table but I'm declaring it before I use it so it's easier to read.

The real power of CTEs is that they can be used recursively so things that used to need loops and cursors can now be achieved using pure Set based code.

Very handy for creating data out of nothing.
 
Here's how to use two CTEs to create data out of nowhere. This creates 1000 rows of data for each day between Jan 1st this year and today.

Code:
DECLARE @Start INT = 1
DECLARE @End INT = 1000
DECLARE @FromDate DATETIME = '2016-01-01 00:00'
DECLARE @ToDate DATETIME = GETDATE()

;WITH NumberSequence(Number) AS
(
    SELECT @start AS Number
        UNION ALL
    SELECT Number + 1
        FROM NumberSequence
        WHERE Number < @end
),
DateSequence(Date) AS
(
    SELECT @FromDate AS Date
        UNION ALL
    SELECT DATEADD(day,1,Date)
        FROM DateSequence
        WHERE Date < @ToDate
)
SELECT ROW_NUMBER() OVER (ORDER BY DateSequence.Date,NumberSequence.Number) AS RowSequence,DateSequence.DATE AS DateSequence
FROM NumberSequence
CROSS JOIN DateSequence
Option (MaxRecursion 32767)
Previously, this would require either a Tally table or Cursor.
 

Bubblin

Well-known Member
Thanks, even though I use sql everyday, I barely scratch the surface on what's possible, thanks for taking the time to help.
 
Probably the smallest query to do the same thing using two correlated subqueries.

This solution will not scale. As the table gets larger this query will table scan it 3 times where as the CTE will do it only once. Much slower with large data sets.

Code:
SELECT a.Name,a.Date,a.Status ,
(SELECT TOP 1 b.Date FROM testtable b WHERE a.Name = b.Name AND a.Date > b.Date ORDER BY b.Date DESC) AS LastDate,
(SELECT TOP 1 b.Status FROM testtable b WHERE a.Name = b.Name AND a.Date > b.Date ORDER BY b.Date DESC) AS LastStatus
FROM testtable a
 

Bubblin

Well-known Member
Probably the smallest query to do the same thing using two correlated subqueries.

This solution will not scale. As the table gets larger this query will table scan it 3 times where as the CTE will do it only once. Much slower with large data sets.

Code:
SELECT a.Name,a.Date,a.Status ,
(SELECT TOP 1 b.Date FROM testtable b WHERE a.Name = b.Name AND a.Date > b.Date ORDER BY b.Date DESC) AS LastDate,
(SELECT TOP 1 b.Status FROM testtable b WHERE a.Name = b.Name AND a.Date > b.Date ORDER BY b.Date DESC) AS LastStatus
FROM testtable a
Your just showing off now ;)
 

Bubblin

Well-known Member
Thank-you, got it working (with a little googling as well) with my example, thanks for your help.

The problem is that MySQL doesn't support the "with" function, so I had to use a derived table to bring back the 'equivalent' of the "with" function.

It worked as I really only need a single pass in my example.
 
Last edited:

Bubblin

Well-known Member
Right, next challenge

I need to download and keep (locally) a copy of all the stored procedures I have (this time in MS SQL 2008 R2)

Is it as simple as

Right clicking the database
selecting Tasks
Selecting Generate Scripts
Select the items to download
run through the wizard/file save location and click run, does this alter anything else on the database apart from downloading them into a file??
 
Right, next challenge

I need to download and keep (locally) a copy of all the stored procedures I have (this time in MS SQL 2008 R2)

Is it as simple as

Right clicking the database
selecting Tasks
Selecting Generate Scripts
Select the items to download
run through the wizard/file save location and click run, does this alter anything else on the database apart from downloading them into a file??
Yep, that's exactly what you need to do.

Things to consider:

By default, it will create CREATE scripts. These will fail if you run them on a database where the SPROCs already exist. You have two options. In the Advanced section look at the options that say

Include If Not Exists
Script Drop and Create

Setting Drop and Create to Drop and Create will first attempt to delete the sproc before creating it essentially overwriting what is there already

Now this too will fail if the SPROC doesn't exist and you try and drop it so you also want to set the Include If Not Exist to True.

Now the scripts will check if each sproc exists. If it does, it will delete it. Then before creating the sproc, it will check again and if it doesn't exist, will create it. With both these setting set, the script is guaranteed not to error regardless of the state of the sprocs on the target server.
 

Bubblin

Well-known Member
Fantastic, and these definitely don't alter or change anything in the database? I'm pretty certain it doesn't but really want to check first?
 
Fantastic, and these definitely don't alter or change anything in the database? I'm pretty certain it doesn't but really want to check first?
Nope, Generate Scripts simply exports scripts to do stuff to the database but the act of creating them does not change anything. You then have to run the resultant scripts to actually change the database.
 

Similar threads

Trending threads

Top Bottom