Hi,

I just try to find a work around for the following query.

SELECT page_id, timestamp, title FROM pages WHERE id IN (SELECT id FROM
pages WHERE workspace_id = 7 ORDER BY timestamp DESC LIMIT 0,1)

When I try to execute this query I get ERROR 1235 that is described at:
http://dev.mysql.com/doc/refman/5.1/...ry-errors.html (top of
page).

Is there anyone who has a creative work around?

The database table includes the following data

+----+---------+---------------------+
| id | page_id | timestamp |
+----+---------+---------------------+
| 11 | 1 | 2007-01-22 16:29:41 |
| 10 | 1 | 2007-01-22 08:30:41 |
| 3 | 1 | 2007-01-21 20:23:55 |
| 7 | 1 | 2007-01-21 15:03:52 |
| 6 | 1 | 2007-01-21 15:03:30 |
| 5 | 3 | 2007-01-21 14:40:00 |
| 4 | 3 | 2007-01-21 14:38:40 |
| 2 | 1 | 2007-01-21 14:29:28 |
| 1 | 1 | 2007-01-21 14:29:14 |
+----+---------+---------------------+

and I like to get only the these entries as a result of my query:

| 5 | 3 | 2007-01-21 14:40:00 |
| 11 | 1 | 2007-01-22 16:29:41 |


I am working with PHP, so it is also fine if you got a solution
involving this. I appreciate all responses!

Thanks
JDD

JDD wrote:

> Hi,
>
> I just try to find a work around for the following query.
>
> SELECT page_id, timestamp, title FROM pages WHERE id IN (SELECT id FROM
> pages WHERE workspace_id = 7 ORDER BY timestamp DESC LIMIT 0,1)
>
> When I try to execute this query I get ERROR 1235 that is described at:
> http://dev.mysql.com/doc/refman/5.1/...ry-errors.html (top of
> page).
>
> Is there anyone who has a creative work around?
>
> The database table includes the following data
>
> +----+---------+---------------------+
> | id | page_id | timestamp |
> +----+---------+---------------------+
> | 11 | 1 | 2007-01-22 16:29:41 |
> | 10 | 1 | 2007-01-22 08:30:41 |
> | 3 | 1 | 2007-01-21 20:23:55 |
> | 7 | 1 | 2007-01-21 15:03:52 |
> | 6 | 1 | 2007-01-21 15:03:30 |
> | 5 | 3 | 2007-01-21 14:40:00 |
> | 4 | 3 | 2007-01-21 14:38:40 |
> | 2 | 1 | 2007-01-21 14:29:28 |
> | 1 | 1 | 2007-01-21 14:29:14 |
> +----+---------+---------------------+
>
> and I like to get only the these entries as a result of my query:
>
> | 5 | 3 | 2007-01-21 14:40:00 |
> | 11 | 1 | 2007-01-22 16:29:41 |
>
>
> I am working with PHP, so it is also fine if you got a solution
> involving this. I appreciate all responses!
>
> Thanks
> JDD
I was going to write a simple JOIN, but then I looked closer at your
query and...
You don't show the values of workspace_id in the above table. But I
cannot see the difference between your query and:
SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7

Captain Paralytic wrote:
> (...) I cannot see the difference between your query and:
> SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7

The query I put above attempts to get the most recent version (recent
timestamp) of every page_id. The query you are suggesting here will
show the whole table. I basically try to write one query that returns
the result of the two queries below (once the program is complete this
will of course include more than two queries):

SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7 AND
page_id = 1 ORDER BY timestamp DESC LIMIT 0,1

SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7 AND
page_id = 3 ORDER BY timestamp DESC LIMIT 0,1

JDD wrote:
> Captain Paralytic wrote:
>> (...) I cannot see the difference between your query and:
>> SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7
>
> The query I put above attempts to get the most recent version (recent
> timestamp) of every page_id. The query you are suggesting here will
> show the whole table. I basically try to write one query that returns
> the result of the two queries below (once the program is complete this
> will of course include more than two queries):
>
> SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7 AND
> page_id = 1 ORDER BY timestamp DESC LIMIT 0,1
>
> SELECT page_id, timestamp, title FROM pages WHERE workspace_id = 7 AND
> page_id = 3 ORDER BY timestamp DESC LIMIT 0,1

I have posted many time about the "Strawberry Query" which is used to ge the
newest or oldest of a group of data (search on "Strawberry Query").

It is not clear exactly how the data is constructed in your table.

Can you post sample data including ALL the referenced columns?

OK, here is the data and the database structure (I have deleted the
data from the column 'text' because it started to look messy and this
column is outside the scope of this problem/challenge anyway).

--------------
Data
--------------

INSERT INTO `pages` (`id`, `workspace_id`, `page_id`, `timestamp`,
`title`, `text`, `user_id`) VALUES (1, 7, 1, '2007-01-21 14:29:14',
'John Deere', '', 7), (2, 7, 1, '2007-01-21 14:29:28', 'John Deere',
'', 7), (3, 7, 1, '2007-01-21 20:23:55', 'John Deere', '', 7), (4, 7,
3, '2007-01-21 14:38:40', 'Projects', '', 7), (5, 7, 3, '2007-01-21
14:40:00', 'Projects', '', 7), (6, 7, 1, '2007-01-21 15:03:30', 'John
Deere', '', 7), (7, 7, 1, '2007-01-21 15:03:52', 'John Deere', '', 7),
(8, 9, 4, '2007-01-21 20:26:31', 'Article', '', 7), (9, 9, 5,
'2007-01-21 20:27:35', 'Pension', '', 7), (10, 7, 1, '2007-01-22
08:30:41', 'John Deere', '', 7), (11, 7, 1, '2007-01-22 16:29:41',
'John Deere', '', 7), (12, 7, 1, '2007-01-22 20:35:19', 'John Deere',
'', 7), (13, 7, 1, '2007-01-22 20:35:40', 'John Deere', '', 7), (14, 7,
1, '2007-01-22 20:57:24', 'John Deere', '', 7), (15, 7, 1, '2007-01-22
21:00:16', 'John Deere', '', 7), (16, 7, 1, '2007-01-22 21:03:22',
'John Deere', '', 7), (17, 7, 1, '2007-01-22 21:41:42', 'John Deere',
'', 7), (18, 7, 1, '2007-01-22 21:44:14', 'John Deere', '', 7);
----------

Thus, the aim is to get the most recent entry (recent timestamp) for
every page_id.

I am trying to understand the strawberry or outer join query, but so
far I haven't got my head around it. Maybe I need get see the example
with this data and then try to play a bit around with it.

Thanks

OK, here is the data and the database structure (I have deleted the
data from the column 'text' because it started to look messy and this
column is outside the scope of this problem/challenge anyway).

--------------
Data and db structure
--------------

CREATE TABLE `pages` (
`id` int(11) NOT NULL auto_increment,
`workspace_id` int(11) default NULL,
`page_id` int(11) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`title` varchar(250) default NULL,
`text` text,
`user_id` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

INSERT INTO `pages` (`id`, `workspace_id`, `page_id`, `timestamp`,
`title`, `text`, `user_id`) VALUES (1, 7, 1, '2007-01-21 14:29:14',
'John Deere', '', 7), (2, 7, 1, '2007-01-21 14:29:28', 'John Deere',
'', 7), (3, 7, 1, '2007-01-21 20:23:55', 'John Deere', '', 7), (4, 7,
3, '2007-01-21 14:38:40', 'Projects', '', 7), (5, 7, 3, '2007-01-21
14:40:00', 'Projects', '', 7), (6, 7, 1, '2007-01-21 15:03:30', 'John
Deere', '', 7), (7, 7, 1, '2007-01-21 15:03:52', 'John Deere', '', 7),
(8, 9, 4, '2007-01-21 20:26:31', 'Article', '', 7), (9, 9, 5,
'2007-01-21 20:27:35', 'Pension', '', 7), (10, 7, 1, '2007-01-22
08:30:41', 'John Deere', '', 7), (11, 7, 1, '2007-01-22 16:29:41',
'John Deere', '', 7), (12, 7, 1, '2007-01-22 20:35:19', 'John Deere',
'', 7), (13, 7, 1, '2007-01-22 20:35:40', 'John Deere', '', 7), (14, 7,
1, '2007-01-22 20:57:24', 'John Deere', '', 7), (15, 7, 1, '2007-01-22
21:00:16', 'John Deere', '', 7), (16, 7, 1, '2007-01-22 21:03:22',
'John Deere', '', 7), (17, 7, 1, '2007-01-22 21:41:42', 'John Deere',
'', 7), (18, 7, 1, '2007-01-22 21:44:14', 'John Deere', '', 7);
----------

Thus, the aim is to get the most recent entry (recent timestamp) for
every page_id.

I am trying to understand the strawberry or outer join query, but so
far I haven't got my head around it. Maybe I need get see the example
with this data and then try to play a bit around with it.

Thanks

JDD wrote:

> OK, here is the data and the database structure (I have deleted the
> data from the column 'text' because it started to look messy and this
> column is outside the scope of this problem/challenge anyway).
>
> --------------
> Data
> --------------
>
> INSERT INTO `pages` (`id`, `workspace_id`, `page_id`, `timestamp`,
> `title`, `text`, `user_id`) VALUES (1, 7, 1, '2007-01-21 14:29:14',
> 'John Deere', '', 7), (2, 7, 1, '2007-01-21 14:29:28', 'John Deere',
> '', 7), (3, 7, 1, '2007-01-21 20:23:55', 'John Deere', '', 7), (4, 7,
> 3, '2007-01-21 14:38:40', 'Projects', '', 7), (5, 7, 3, '2007-01-21
> 14:40:00', 'Projects', '', 7), (6, 7, 1, '2007-01-21 15:03:30', 'John
> Deere', '', 7), (7, 7, 1, '2007-01-21 15:03:52', 'John Deere', '', 7),
> (8, 9, 4, '2007-01-21 20:26:31', 'Article', '', 7), (9, 9, 5,
> '2007-01-21 20:27:35', 'Pension', '', 7), (10, 7, 1, '2007-01-22
> 08:30:41', 'John Deere', '', 7), (11, 7, 1, '2007-01-22 16:29:41',
> 'John Deere', '', 7), (12, 7, 1, '2007-01-22 20:35:19', 'John Deere',
> '', 7), (13, 7, 1, '2007-01-22 20:35:40', 'John Deere', '', 7), (14, 7,
> 1, '2007-01-22 20:57:24', 'John Deere', '', 7), (15, 7, 1, '2007-01-22
> 21:00:16', 'John Deere', '', 7), (16, 7, 1, '2007-01-22 21:03:22',
> 'John Deere', '', 7), (17, 7, 1, '2007-01-22 21:41:42', 'John Deere',
> '', 7), (18, 7, 1, '2007-01-22 21:44:14', 'John Deere', '', 7);
> ----------
>
> Thus, the aim is to get the most recent entry (recent timestamp) for
> every page_id.
>
> I am trying to understand the strawberry or outer join query, but so
> far I haven't got my head around it. Maybe I need get see the example
> with this data and then try to play a bit around with it.
>
> Thanks

I ***ume that with this data, you would expect rows 5 and 18 rather
than 5 and 11 as suggested earlier. I think that the following query
(based on the "Strawberry Query" should do what you want.

In future, try to give the full information about a problem. It is very
difficult to relate data to a query and a result when all the columns
aren't shown and when the question does not state what is special about
the results shown.

SELECT
`p1`.`id` , `p1`.`page_id` , `p1`.`timestamp`
FROM `pages` `p1`
LEFT JOIN `pages` `p2` ON `p1`.`page_id` = `p2`.`page_id` AND
`p1`.`timestamp` < `p2`.`timestamp`
WHERE `p2`.`timestamp` IS NULL
AND `p1`.`workspace_id` =7

Thank you very much. I got to work what I wanted. I ***umed that my
first post included enough information so somebody could provide help.
However, as shown this did not happen to be the case.

Do you have a suggestion what to read to understand a bit more of the
background of this solution?

Thank you very much. I got to work what I wanted. I ***umed that my
first post included enough information so somebody could provide help.
However, as shown this did not happen to be the case.

Do you have a suggestion what to read to understand a bit more of the
background of this solution?

JDD wrote:

> Thank you very much. I got to work what I wanted. I ***umed that my
> first post included enough information so somebody could provide help.
> However, as shown this did not happen to be the case.
>
> Do you have a suggestion what to read to understand a bit more of the
> background of this solution?

I'm sure that this construct is explained somewhere else in this forum,
but I'll run through it anyway:

First of all let's look at the JOIN.
A join is usually written as
Table_A JOIN Table_B ON join_conditions

This will cause MySQL to attempt to match each record from Table_A with
one or more records from Table_B based on the join_conditions. If the
join_conditions are satisfied, then a row is output containing
populated fields from the matched records from both Table_A and
Table_B. If the join_conditions are not satisfied then no row is output
for that particular row from Table_A, we move on to the next record in
Tabl;e_A and the match is attempted again, and so on.

Now we have used a LEFT JOIN. This is similar to a JOIN with the
following important difference. If the join_conditions are satisfied
then the operation is the same as for the JOIN above. If it is not then
the data for the row from Table_A is output, but all the fields for
Table_B will be NULL.

So, if you wanted to find what records in Table_A did not have matching
records in Table_B, you could test one of the non-NULL fields used in
the join_conditions to see if it was NULL. If is was NULL then you
would output the record from Table_A.

Now, in this particular scenario we have joined a table to itself using
the table aliases p1 and p2. Think of these as being 2 separate tables
that just happen to contain exactly the same data. The join_conditions
are:

LEFT JOIN `pages` `p2` ON `p1`.`page_id` = `p2`.`page_id` AND
`p1`.`timestamp` < `p2`.`timestamp`

So let's look at what happens. Since the two tables p1 and p2 are
identical, we are guaranteed that the first condition will always be
satisfied as there will always be a record in p2 with the same page_id
as any record in p1.
However the second condition says that in order to match, the timestamp
of a record in p2 must be greater than (i.e. newer) than the record
that we are currently processing in p1. The only time that this is not
true is when the record in p1 is the newest record for a particular
page_id. In this case, no match will be found and thus all the fields
from p2 will be set to NULL.

In the WHERE clause we check:

WHERE `p2`.`timestamp` IS NULL

And thus output only those records with the newest timestamps. In this
case we could have used the WHERE clause

WHERE `p2`.`page_id` IS NULL

and got the same result.

Phew, hope that's clear!