newbie question on groups

I have:
col1 col2
item1 3
item1 5
item2 7
item2 3
item1 4
item3 4

I'm looking for the result:
col1 col2
item1 5
item2 7
item3 4

--the highest col2 value for data paired with col1
--no duplicates in col1
--order does not matter for either col1 or col2 in the result

The SQL I have now is:
select col1, col2 from table1 group by col1

I'm not sure how to force it to group col1 using the highest value from
col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if
this is relevant, but table1 is made by joining two other tables
together (one of which is a heap).

Thanks in advance!

Re: newbie question on groups

sbswift@gmail.com wrote:
> I have:
> col1 col2
> item1 3
> item1 5
> item2 7
> item2 3
> item1 4
> item3 4
>
> I'm looking for the result:
> col1 col2
> item1 5
> item2 7
> item3 4
>
> --the highest col2 value for data paired with col1
> --no duplicates in col1
> --order does not matter for either col1 or col2 in the result
>
> The SQL I have now is:
> select col1, col2 from table1 group by col1
>
> I'm not sure how to force it to group col1 using the highest value from
> col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if
> this is relevant, but table1 is made by joining two other tables
> together (one of which is a heap).
>
> Thanks in advance!


SELECT t1 . *
FROM table1 t1
LEFT JOIN table1 t2 ON t1.col1 = t2.col1
AND t1.col2 < t2.col2
WHERE t2.col2 IS NULL;

Re: newbie question on groups

sbswift@gmail.com wrote:
> I have:
> col1 col2
> item1 3
> item1 5
> item2 7
> item2 3
> item1 4
> item3 4
>
> I'm looking for the result:
> col1 col2
> item1 5
> item2 7
> item3 4
>
> --the highest col2 value for data paired with col1
> --no duplicates in col1
> --order does not matter for either col1 or col2 in the result
>
> The SQL I have now is:
> select col1, col2 from table1 group by col1
>
> I'm not sure how to force it to group col1 using the highest value from
> col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if
> this is relevant, but table1 is made by joining two other tables
> together (one of which is a heap).
>
> Thanks in advance!

How about:

mysql> create table zetest ( col1 varchar(16) not null, col2 int not
null ) ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into zetest values('item1',3), ('item1',5), ('item2',7),
('item2',3), ('item1',4), ('item3',4) ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select col1, max(col2) from zetest group by 1 order by 1 ;
+-------+-----------+
| col1 | max(col2) |
+-------+-----------+
| item1 | 5 |
| item2 | 7 |
| item3 | 4 |
+-------+-----------+
3 rows in set (0.01 sec)

Re: newbie question on groups

shakahshakah@gmail.com wrote:
> sbswift@gmail.com wrote:
> > I have:
> > col1 col2
> > item1 3
> > item1 5
> > item2 7
> > item2 3
> > item1 4
> > item3 4
> >
> > I'm looking for the result:
> > col1 col2
> > item1 5
> > item2 7
> > item3 4
> >
> > --the highest col2 value for data paired with col1
> > --no duplicates in col1
> > --order does not matter for either col1 or col2 in the result
> >
> > The SQL I have now is:
> > select col1, col2 from table1 group by col1
> >
> > I'm not sure how to force it to group col1 using the highest value from
> > col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if
> > this is relevant, but table1 is made by joining two other tables
> > together (one of which is a heap).
> >
> > Thanks in advance!
>
> How about:
>
> mysql> create table zetest ( col1 varchar(16) not null, col2 int not
> null ) ;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> insert into zetest values('item1',3), ('item1',5), ('item2',7),
> ('item2',3), ('item1',4), ('item3',4) ;
> Query OK, 6 rows affected (0.00 sec)
> Records: 6 Duplicates: 0 Warnings: 0
>
> mysql> select col1, max(col2) from zetest group by 1 order by 1 ;
> +-------+-----------+
> | col1 | max(col2) |
> +-------+-----------+
> | item1 | 5 |
> | item2 | 7 |
> | item3 | 4 |
> +-------+-----------+
> 3 rows in set (0.01 sec)

Cool. But mine seemed simper somehow

Re: newbie question on groups

strawberry wrote:
> shakahshakah@gmail.com wrote:
> > sbswift@gmail.com wrote:
> > > I have:
> > > col1 col2
> > > item1 3
> > > item1 5
> > > item2 7
> > > item2 3
> > > item1 4
> > > item3 4
> > >
> > > I'm looking for the result:
> > > col1 col2
> > > item1 5
> > > item2 7
> > > item3 4
> > >
> > > --the highest col2 value for data paired with col1
> > > --no duplicates in col1
> > > --order does not matter for either col1 or col2 in the result
> > >
> > > The SQL I have now is:
> > > select col1, col2 from table1 group by col1
> > >
> > > I'm not sure how to force it to group col1 using the highest value from
> > > col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if
> > > this is relevant, but table1 is made by joining two other tables
> > > together (one of which is a heap).
> > >
> > > Thanks in advance!
> >
> > How about:
> >
> > mysql> create table zetest ( col1 varchar(16) not null, col2 int not
> > null ) ;
> > Query OK, 0 rows affected (0.02 sec)
> >
> > mysql> insert into zetest values('item1',3), ('item1',5), ('item2',7),
> > ('item2',3), ('item1',4), ('item3',4) ;
> > Query OK, 6 rows affected (0.00 sec)
> > Records: 6 Duplicates: 0 Warnings: 0
> >
> > mysql> select col1, max(col2) from zetest group by 1 order by 1 ;
> > +-------+-----------+
> > | col1 | max(col2) |
> > +-------+-----------+
> > | item1 | 5 |
> > | item2 | 7 |
> > | item3 | 4 |
> > +-------+-----------+
> > 3 rows in set (0.01 sec)
>
> Cool. But mine seemed simper somehow

Simper !?!?!?

Simpler ;-)