How to get latest record for each day when there are multiple entries per day?

Question

I have table called dg like this:

name    kw      date
----    ---     -------------------
dg1     0       2008-10-16 01:08:09
dg1     213     2008-10-16 02:06:09
dg1     135     2008-10-16 23:05:09
dg1     0       2008-10-17 01:08:09
dg1     213     2008-10-17 02:06:09
dg2     0       2008-10-16 03:08:09
dg2     213     2008-10-16 04:06:09
dg2     135     2008-10-16 22:05:09
dg2     0       2008-10-17 04:08:09
dg2     213     2008-10-17 05:06:09

I need the latest record for each day for a particular name (e.g dg1) & date beween x and y.

For example, I need a query to get the last updated record for each day, like below for name dg1:

name    kw      date
----    ---      -------------------
dg1     135     2008-10-16 23:05:09
dg1     213     2008-10-17 02:06:09

I'm using MySQL.

Answer 1

What language is this?

Answer 2

I need a query in MySQL, please.

Answer 3

I need a query to get the last updated record for each day, like below for name dg1:

name   kw           date
dg1     135     2008-10-16 23:05:09
dg1     213     2008-10-17 02:06:09

(Please learn how to indent tabular data and code - also look at the preview!)

Answer 4

If you had an extra column in the data, call it just_date, containing just the date (year, month, day) part of the values shown, then you'd write:

SELECT name, just_date, MAX(date)
    FROM SomeAnonymousTable
    GROUP BY name, just_date;

So, how do you get just the date portions of a 'date' that includes time pieces...

This gets DBMS-specific, and I didn't know the details for MySQL. However, a web search found me the same answer that @Ipfavreau commented - the DATE() function:

SELECT name, DATE(date) AS just_date, MAX(date)
    FROM SomeAnonymousTable
    GROUP BY name, just_date;

---

The question was clarified...the previous answer is only part of the answer.

The query above identifies the rows that must be selected; now we need to pull out the kw column value that matches. We cannot just add kw into the select-list because we'd also have to list it in the GROUP BY clause (in standard SQL, at any rate). So, we need to embed the grouping query into the FROM clause:

SELECT a.name, a.kw, a.date
    FROM SomeAnonymousTable AS a,
        (SELECT name, DATE(date) AS just_date, MAX(date)
            FROM SomeAnonymousTable
            GROUP BY name, just_date
        ) AS b
    WHERE a.name = b.name
      AND a.date = b.date;

The code above was untested on any DBMS. Note that it is a bad idea to use a keyword (type, and function name) as a column name. Even if it is allowed, it is confusing. Also, please remember to give the table a name next time.

Update: Now tested on IBM Informix Dynamic Server (IDS) 11.50. An amended version of the query works on table dg, as noted in the edits to another answer. The 'date' column was renamed to upd_time. For better or worse, IDS requires the positional notation (1, 2) in the GROUP BY clause.

SELECT a.name, a.kw, a.upd_time
    FROM dg AS a,
        (SELECT name, DATE(upd_time) AS just_date, MAX(upd_time) AS upd_time
            FROM dg
            GROUP BY 1, 2 -- name, DATE(upd_time)
        ) AS b
    WHERE a.name = b.name
      AND a.upd_time = b.upd_time;

This modified query yields what seem to be the correct results:

dg1   135   2008-10-16 23:05:09
dg1   213   2008-10-17 02:06:09
dg2   135   2008-10-16 22:05:09
dg2   213   2008-10-17 05:06:09

This working version shows an area where different DBMS work slightly differently. Sometimes you can use the column aliases (sometimes called 'display labels'; the names after AS in the select-list) inside the query, and sometimes you can't. It depends on the DBMS in question, and maybe also on the level of the SQL standard that is supported. The positional notation (GROUP BY 1, 2 -- and the similar ORDER BY 1, 2) is not the preferred notation where names work. But when names don't work, you have to use whatever it takes to get the job done.

Answer 5

Thanks a lot Jonathan. I have given the following query.

SELECT name, kw, LPAD(date, 10,' '), MAX(date)
    FROM dg  WHERE name = 'dg1'
    GROUP BY name, LPAD(date, 10,' ');

It works. But I did not understand how it picks up the latest record for each day.

Answer 6

Thanks lot Jonathan.

(Please indent all code by 4 spaces - it makes the layout look better. Also, look at the preview - it shows you what your question or answer will look like!)

I have executed the following query:

SELECT a.name, a.kw, a.date
    FROM dg AS a,
        (SELECT name, LPAD(date, 10,' ') AS just_date, MAX(date) AS date
            FROM dg
            WHERE name ='dg1'
            GROUP BY just_date
        ) AS b
    WHERE a.name = b.name
      AND a.date = b.date
    ORDER BY a.date;

but there are some duplicate rows. I tried with 'order by b.date' also. Still there are duplicate rows. Can you please help me?

---

Jonathan writing:

I tested the query on IBM Informix Dynamic Server (IDS) 11.50, and had to use a different notation in the GROUP BY clause. I also renamed the 'date' column to 'upd_time'. With those changes and your original data set, I got an answer that I consider correct:

SELECT a.name, a.kw, a.upd_time
    FROM dg AS a,
        (SELECT name, DATE(upd_time) AS just_date, MAX(upd_time) AS upd_time
            FROM dg
            GROUP BY 1, 2 --name, DATE(upd_time)
        ) AS b
    WHERE a.name = b.name
      AND a.upd_time = b.upd_time;

dg1   135   2008-10-16 23:05:09
dg1   213   2008-10-17 02:06:09
dg2   135   2008-10-16 22:05:09
dg2   213   2008-10-17 05:06:09

Given that this produces the correct answer on my system, I'm left to conclude that omission of the second column in the GROUP BY clause matters, or the LPAD formulation is not giving the correct result (have you looked at the intermediate result?), or the MySQL query optimizer you are using is mishandling this query (aka bug). I'm not sure which is preferable. Try the query with both the grouping columns listed -- not just one.

Answer 7

Just use 'top' and 'order by' clauses.

select top 1 * from [table] where ... order by mydatecolumn

Answer 8

You can do it with the query people write you, or much easier way will be to use a free Data Profiler (for SQL or MySQL)

Answer 9

dgI know this question is about MySQL. But for people looking for this same problem solution for Oracle or MSSQL, using row_number() over partition by... would be more efficient since there are no joins. So, we can do something like that:

select name, kw, post_date,
    row_number() over
       (partition by
            name,
            just_date
          order by
            name,
            post_date desc) as lastest_order
  from
  (
    select
        name,
        kw,
        post_date,
        trunc(post_date) as just_date
      from dg
   )

so the result would be

name    kw    post_date               lastest_order
----    ---   -------------------     -------------
dg1     135   10/16/2008 23:05:09     1
dg1     213   10/16/2008 02:06:09     2
dg1     0     10/16/2008 01:08:09     3
dg1     213   10/17/2008 02:06:09     1
dg1     0     10/17/2008 01:08:09     2
dg2     135   10/16/2008 22:05:09     1
dg2     213   10/16/2008 04:06:09     2
dg2     0     10/16/2008 03:08:09     3
dg2     213   10/17/2008 05:06:09     1
dg2     0     10/17/2008 04:08:09     2

and then all we have to do is to filter out rows where lastest_order = 1