CODEWITHSUNDEEP
mysqlgroup-by
Paul
Paul

Reputation: 9012

How to get the latest set of data available out of a MySQL table (data sets are stored daily)?

I have a MySQL table with following setup:

CREATE TABLE IF NOT EXISTS `coords` (
  `project_id` int(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `x` tinyint(4) NOT NULL,
  `y` tinyint(4) NOT NULL,
  `z` tinyint(4) NOT NULL,
  PRIMARY KEY (`tid`,`day`,`x`,`y`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Each project is running several days. Each day some cases are tested and the results (set of coordinates) are saved in this table (along with project id and day number). The same set of x and y coordinates can be saved more often than once (the z coordinate is the actual result).

Now I want to get all available sets of coordinates for a single project with the latest data available.

I.e., assuming I have following data:

pid | day | x | y | z
  1 |   1 | 2 | 1 | 5
  1 |   1 | 2 | 2 | 6
  1 |   2 | 3 | 3 | 7
  1 |   3 | 2 | 1 | 8
  1 |   3 | 3 | 3 | 9

The query shall now return:

day | x | y | z
  1 | 2 | 2 | 6
  3 | 2 | 1 | 8
  3 | 3 | 3 | 9

I was trying following query:

SELECT day, x, y, z 
FROM coords 
WHERE pid = 1 
GROUP BY CONCAT(tid,'.',x,'.',y) 
ORDER BY day DESC

But this query returns old data, just as day = 1, x|y|z = 2|1|5 instead of day = 3 and x|y|z = 2|1|8.

Could someone please point me in the right direction of what I am missing here?

Thanks in advance, Paul

Upvotes: 0

Views: 423

Answers (2)

Jan
Jan

Reputation: 2293

Try this out :).

SELECT
  day,
  x,
  y,
  z 
FROM
  coords
    JOIN
  (SELECT MAX(tid) AS max_tid FROM coords GROUP BY CONCAT_WS('.', day, x, y)) sub ON (sub.max_tid = coords.tid)
WHERE
  pid = 1

Upvotes: 1

exus
exus

Reputation: 215

well i thing you have a problem there because once you do Select * from coords order by day asc limit 1 you will get randomly. 2.1.5 or 2.2.6. depending on how the database feels it. You definitly need more specific "dayting" or ID or something according to what will the database know in what order those lines were inserted in. Add an id field there for example.

Select distinct day, x,y,z 
FROM coords
where pid = 1
order by id desc

This might do the magic if you add the id field in there

Upvotes: 1

Related Questions