By markdotnet


2014-05-30 19:09:22 8 Comments

The base problem I'm facing is that I need the latest record for each item.

A little setup... MySQL 5.6.14.

I need to create two views (because MySQL won't let me have a subquery in a view). My first query sets up the data like this.

select 
    `inventoryrecords`.`inventoryrecordid` AS `inventoryrecordid`,
    `inventoryrecords`.`logicaldeviceid` AS `logicaldeviceid`,
    `inventoryrecords`.`passrfid` AS `passrfid`,
    `inventoryrecords`.`tagepc` AS `tagepc`,
    `inventoryrecords`.`currentstate` AS `currentstate`,
    `inventoryrecords`.`statedateutc` AS `statedateutc`,
    `inventoryrecords`.`ownerobjectid` AS `ownerobjectid`,
    `inventoryrecords`.`ownerobjecttype` AS `ownerobjecttype`
from
    `inventoryrecords`
where
    1
order by `inventoryrecords`.`statedateutc` desc

Then I can restrict everything to just the last record for each TagEPC with my "real" query like this.

select 
    `lastinventoryrecords_step1`.`inventoryrecordid` AS `inventoryrecordid`,
    `lastinventoryrecords_step1`.`logicaldeviceid` AS `logicaldeviceid`,
    `lastinventoryrecords_step1`.`passrfid` AS `passrfid`,
    `lastinventoryrecords_step1`.`tagepc` AS `tagepc`,
    `lastinventoryrecords_step1`.`currentstate` AS `currentstate`,
    `lastinventoryrecords_step1`.`statedateutc` AS `statedateutc`,
    `lastinventoryrecords_step1`.`ownerobjectid` AS `ownerobjectid`,
    `lastinventoryrecords_step1`.`ownerobjecttype` AS `ownerobjecttype`
from
    `lastinventoryrecords_step1`
group by `lastinventoryrecords_step1`.`tagepc`
order by `lastinventoryrecords_step1`.`statedateutc` desc

When I try to select * from the "real" view, I do not get the data I expect. However when I run the query with the sub-query in a window I do.

select * from ( 
lastinventoryrecords_step1 )
group by tagepc
order by statedateutc desc

yields incorrect data whereas

select * from (
    select 
        inventoryrecordid,
        logicaldeviceid,
        passrfid,
        tagepc,
        currentstate,
        statedateutc,
        ownerobjectid,
        ownerobjecttype
    from
        inventoryrecords
    where
        1
    order by statedateutc desc ) as t1
group by tagepc
order by statedateutc desc

yields the right data.

What in the heck is going on? What am I missing?

1 comments

@Bill Karwin 2014-05-30 20:24:33

You're using GROUP BY in a non-deterministic way. You GROUP BY tagepc, but what value for the other columns do you expect?

MySQL permits this kind of query, but MySQL chooses a row from the group arbitrarily. In practice, it tends to be the first row in the group, as they are stored physically.

But in the case when you query through the view, the view stores its results in a temporary table, probably in a different physical order than the data in the original table.

Note that http://dev.mysql.com/doc/refman/5.6/en/create-view.html says:

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

So the query run by the view to populate the temp table ignores your view's ORDER BY, and since you used GROUP BY it probably takes values from arbitrary rows in the underlying table.

How to fix this? Stop running ambiguous GROUP BY queries. Any column referenced in your select-list should either by (a) listed in the GROUP BY clause, or (b) inside a grouping function like MAX() or something.

I don't know what you expect this query to do, or why you're using GROUP BY in the first place. So I can't offer any more specific advice.


Re your comment:

Thanks for the clarification. This is a pretty common type of SQL question. I created the tag on StackOverflow and now it has close to a thousand questions. The tag exists on dba.stackexchange.com, but with fewer questions so far.

Here's a solution:

SELECT i.*
FROM inventoryrecords AS i
JOIN (SELECT TagEPC, MAX(StateDateUTC) AS StateDateUTC
      FROM inventoryrecords
      GROUP BY TagEPC ORDER BY NULL) AS maxi USING (TagEPC, StateDateUTC);

@markdotnet 2014-06-02 13:33:01

Thanks for that. What I'm trying to do is this... each record has a TagEPC, a CurrentState and a StateDateUTC. The data models a product being put into or taken out of a cabinet. The TagEPC is the product, the Current State is whether the item is In or Out and the State Date is when it happened. I want the latest record (based on StateDateUTC) for each TagEPC. I found I can get what I want if I don't use a View, but my query is just ungodly ugly.

Related Questions

Sponsored Content

1 Answered Questions

Mariadb started giving erroneous query results for 1 view

2 Answered Questions

[SOLVED] MySQL slow select from a view

2 Answered Questions

[SOLVED] MySQL: Grouping with condition

  • 2018-01-23 12:50:41
  • evilReiko
  • 506 View
  • 2 Score
  • 2 Answer
  • Tags:   mysql join group-by

1 Answered Questions

Omit made up columns from results

  • 2016-10-09 04:15:33
  • helllomatt
  • 17 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql

1 Answered Questions

[SOLVED] Insert into a view derived from a table with a discriminator field

  • 2014-05-29 12:58:50
  • Matteo Tassinari
  • 3177 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql view mysql-5.1

2 Answered Questions

[SOLVED] Multiple-View Query Optimisation

2 Answered Questions

[SOLVED] MySQL security group view permission query

  • 2015-06-06 02:52:06
  • Levi Putna
  • 118 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql

Sponsored Content