Professional Web Applications Themes

Joining tables on highest entry only - MySQL

Hi, I have two tables as follows: mysql> show create table top; | top | CREATE TABLE `top` ( `top_no` int(10) unsigned NOT NULL auto_increment, `created` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`top_no`) ) ENGINE=MyISAM AUTO_INCREMENT=147 DEFAULT CHT=latin1 ROW_FORMAT=FIXED | mysql> show create table top_status; | top_status | CREATE TABLE `top_status` ( `top_no` int(10) unsigned NOT NULL default '0', `top_seq` tinyint(3) unsigned NOT NULL auto_increment, `status` tinyint(3) unsigned NOT NULL default '0', `updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`todo_no`,`todo_no_seq`) ) ENGINE=MyISAM DEFAULT CHT=latin1 ROW_FORMAT=FIXED | I've snipped other unnecessary data to simplify matters. For each ...

  1. #1

    Default Joining tables on highest entry only

    Hi, I have two tables as follows:

    mysql> show create table top;

    | top | CREATE TABLE `top` (
    `top_no` int(10) unsigned NOT NULL auto_increment,

    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`top_no`)
    ) ENGINE=MyISAM AUTO_INCREMENT=147 DEFAULT CHT=latin1 ROW_FORMAT=FIXED
    |

    mysql> show create table top_status;

    | top_status | CREATE TABLE `top_status` (
    `top_no` int(10) unsigned NOT NULL default '0',
    `top_seq` tinyint(3) unsigned NOT NULL auto_increment,
    `status` tinyint(3) unsigned NOT NULL default '0',

    `updated` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`todo_no`,`todo_no_seq`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1 ROW_FORMAT=FIXED |

    I've snipped other unnecessary data to simplify matters.

    For each entry in `top` there's likely to be around half a dozen or so
    entries in `top_status`, but there could be as few as one, or in theory at
    least as many as 250, though the current maximum is 47.

    I'm trying to write a selection that will give me just the single highest
    top_seq entry in top_status for each entry in top.

    That is, given that `top` has the following 3 entries:

    mysql> select top_no,created from top limit 3;
    +--------+---------------------+
    | top_no | created |
    +--------+---------------------+
    | 14 | 2005-08-29 13:30:02 |
    | 15 | 2005-08-29 13:31:00 |
    | 16 | 2005-08-29 13:32:43 |
    +--------+---------------------+
    3 rows in set (0.01 sec)

    mysql> select top_no,top_seq,status,updated from top_status where
    top_no < 17;
    +--------+---------+--------+---------------------+
    | top_no | top_seq | status | updated |
    +--------+---------+--------+---------------------+
    | 14 | 1 | 1 | 2005-09-24 12:24:32 |
    | 14 | 2 | 1 | 2005-09-24 12:26:19 |
    | 14 | 3 | 2 | 2005-09-24 12:27:38 |
    | 14 | 4 | 2 | 2005-09-26 09:04:17 |
    | 14 | 5 | 2 | 2005-09-26 09:24:16 |
    | 14 | 6 | 17 | 2005-11-07 17:18:23 |
    | 14 | 7 | 2 | 2005-11-07 17:18:33 |
    | 15 | 1 | 0 | 2005-08-29 13:31:00 |
    | 15 | 2 | 1 | 2005-09-24 12:12:45 |
    | 15 | 3 | 1 | 2005-09-24 12:13:22 |
    | 15 | 4 | 1 | 2005-09-26 09:01:26 |
    | 16 | 1 | 0 | 2005-08-29 13:32:43 |
    | 16 | 2 | 1 | 2005-09-24 14:06:11 |
    | 16 | 3 | 2 | 2005-10-25 16:32:49 |
    | 16 | 4 | 3 | 2005-11-01 16:34:42 |
    | 16 | 5 | 3 | 2007-05-09 17:55:39 |
    +--------+---------+--------+---------------------+
    16 rows in set (0.01 sec)

    I want a query that will result in:

    +--------+---------+--------+---------------------+---------------------+
    | top_no | top_seq | status | updated | created |
    +--------+---------+--------+---------------------+---------------------+
    | 14 | 7 | 2 | 2005-11-07 17:18:33 | 2005-08-29 13:30:02 |
    | 15 | 4 | 1 | 2005-09-26 09:01:26 | 2005-08-29 13:31:00 |
    | 16 | 5 | 3 | 2007-05-09 17:55:39 | 2005-08-29 13:32:43 |
    +--------+---------+--------+---------------------+---------------------+

    Can anyone offer any clues please?

    Many thanks,

    Dave

    --
    Dave Stratford ZFCA
    http://daves.orpheusweb.co.uk/
    Hexagon Systems Limited - Experts in VME systems development

    Dave Guest

  2. #2

    Default Re: Joining tables on highest entry only

    On 27 May, 11:12, Dave Stratford <co.uk> wrote: 


    SELECT t1.* FROM my_table t1
    LEFT JOIN my_table t2
    t1.group_id = t2.group_id
    AND t1.item_id < t2.item_id
    WHERE t2.item_id IS NULL;

    strawberry Guest

  3. #3

    Default Re: Joining tables on highest entry only

    In article <googlegroups.com>,
    strawberry <com> wrote: [/ref]

    [snip]
     [/ref]

     

    Thanks very much for the reply. Can you explain this please as I don't
    understand what it's trying to do.

    I have got it to work, I think, but it would help if I actually understood
    it.

    Many Thanks,

    Dave

    --
    Dave Stratford ZFCA
    http://daves.orpheusweb.co.uk/
    Hexagon Systems Limited - Experts in VME systems development

    Dave Guest

  4. #4

    Default Re: Joining tables on highest entry only

    On 27 May, 13:34, Dave Stratford <co.uk> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    >
    > [snip]
    >
    >
    > [/ref]
    > [/ref]
    > [/ref]

    > > SELECT t1.* FROM my_table t1
    > > LEFT JOIN my_table t2
    > > t1.group_id = t2.group_id
    > > AND t1.item_id < t2.item_id
    > > WHERE t2.item_id IS NULL;[/ref]
    >
    > Thanks very much for the reply. Can you explain this please as I don't
    > understand what it's trying to do.
    >
    > I have got it to work, I think, but it would help if I actually understood
    > it.
    >
    > Many Thanks,
    >
    > Dave
    >
    > --
    > Dave Stratford ZFCAhttp://daves.orpheusweb.co.uk/
    > Hexagon Systems Limited - Experts in VME systems development[/ref]

    This from the manual:

    "The LEFT JOIN works on the basis that when [s1.item_id] is at its
    maximum value, there is no [s2.item_id] with a greater value and the
    s2 rows values will be NULL. See Section 13.2.7.1, "JOIN Syntax"."

    To understand this better it's helpful to rewrite the query with this
    syntax:

    SELECT * FROM my_table t1
    LEFT JOIN my_table t2
    t1.group_id = t2.group_id
    AND t1.item_id < t2.item_id;


    strawberry Guest

Similar Threads

  1. Joining three tables
    By DettCom in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 19th, 06:12 PM
  2. Joining tables, Please help
    By Student_bob in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 08:49 AM
  3. joining 3 tables?
    By siti_nana in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 27th, 08:16 AM
  4. joining several tables
    By james in forum IBM DB2
    Replies: 3
    Last Post: October 17th, 01:42 PM
  5. HELP -- joining tables
    By JVince in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 08:09 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