Professional Web Applications Themes

Work around ERROR 1235 (LIMIT in subquery) - MySQL

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/en/subquery-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 ...

  1. #1

    Default Work around ERROR 1235 (LIMIT in subquery)

    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/en/subquery-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 Guest

  2. #2

    Default Re: Work around ERROR 1235 (LIMIT in subquery)


    JDD wrote:
     
    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 Guest

  3. #3

    Default Re: Work around ERROR 1235 (LIMIT in subquery)


    Captain Paralytic wrote: 

    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 Guest

  4. #4

    Default Re: Work around ERROR 1235 (LIMIT in subquery)

    JDD wrote: 
    >
    > 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[/ref]

    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?


    Paul Guest

  5. #5

    Default Re: Work around ERROR 1235 (LIMIT in subquery)

    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

    JDD Guest

  6. #6

    Default Re: Work around ERROR 1235 (LIMIT in subquery)

    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 CHT=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 Guest

  7. #7

    Default Re: Work around ERROR 1235 (LIMIT in subquery)


    JDD wrote:
     

    I assume 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

    Captain Guest

  8. #8

    Default Re: Work around ERROR 1235 (LIMIT in subquery)

    Thank you very much. I got to work what I wanted. I assumed 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 Guest

  9. #9

    Default Re: Work around ERROR 1235 (LIMIT in subquery)

    Thank you very much. I got to work what I wanted. I assumed 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 Guest

  10. #10

    Default Re: Work around ERROR 1235 (LIMIT in subquery)


    JDD wrote:
     

    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!

    Captain Guest

Similar Threads

  1. Replies: 0
    Last Post: July 23rd, 12:15 AM
  2. 16 character password limit; need work-around
    By quasigenx in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 29th, 04:07 PM
  3. Redirection limit error
    By johndoe01 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 24th, 09:10 PM
  4. Replies: 0
    Last Post: January 7th, 10:56 PM
  5. #25127 [Fbk->Opn]: error with memory limit
    By henrik dot gebauer at web dot de in forum PHP Development
    Replies: 0
    Last Post: August 19th, 11:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139