Joining tables on highest entry only

**Joining tables on highest entry only -** 06-04-2007, 07:58 AM

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 CHARSET=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 CHARSET=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

**Re: Joining tables on highest entry only -** 06-04-2007, 07:58 AM

On 27 May, 11:12, Dave Stratford <d...@orpheusmail.co.uk> wrote:
> 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 CHARSET=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 CHARSET=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 ZFCAhttp://daves.orpheusweb.co.uk/
> Hexagon Systems Limited - Experts in VME systems development

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;

**Re: Joining tables on highest entry only -** 06-04-2007, 07:58 AM

In article <EMAIL REMOVED .com>,
strawberry <EMAIL REMOVED> wrote:
> On 27 May, 11:12, Dave Stratford <d...@orpheusmail.co.uk> wrote:
> > 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 CHARSET=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 CHARSET=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:
> >

[snip]

> >
> > Can anyone offer any clues please?
> >
> > Many thanks,
> >
> > Dave
> >
> > --
> > Dave Stratford ZFCAhttp://daves.orpheusweb.co.uk/
> > Hexagon Systems Limited - Experts in VME systems development

> 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;

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

**Re: Joining tables on highest entry only -** 06-04-2007, 07:58 AM

On 27 May, 13:34, Dave Stratford <d...@orpheusmail.co.uk> wrote:
> In article <1180261881.501504.296...@k79g2000hse.googlegroups .com>,
> strawberry <zac.ca...@gmail.com> wrote:
>
>
>
> > On 27 May, 11:12, Dave Stratford <d...@orpheusmail.co.uk> wrote:
> > > 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 CHARSET=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 CHARSET=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:
>
> [snip]
>
>
>
> > > Can anyone offer any clues please?
>
> > > Many thanks,
>
> > > Dave
>
> > > --
> > > Dave Stratford ZFCAhttp://daves.orpheusweb.co.uk/
> > > Hexagon Systems Limited - Experts in VME systems development
> > 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;
>
> 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

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;