query to grab great value in column for each id

Given a table of data such as this

id,size
'0001', 'S'
'0001', 'M'
'0002', 'L'
'0002', 'XL'
'0003', 'L'
'0004', 'L'
'0005', 'S'
'0005', 'M'
'0005', 'XL'

how would you write a query that will return the the greatest value of the 2nd column (size) for EACH ID.

XL > L > M > S

The correct output would be:

'0001', 'M'
'0002', 'XL'
'0003', 'L'
'0004', 'L'
'0005', 'XL'

It there a single, elegant query that can do such a thing?
mock5cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flob9Commented:
CREATE TABLE  `test`.`t5` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `size` enum('S','M','L','XL') NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT * FROM `t5` order by size

=>
1, 'S'
3, 'M'
4, 'L'
2, 'XL'
0
flob9Commented:
(sorted by enum index)

Another solution : create a table "size" with a column named "order", then make a join and order by this column.
0
flob9Commented:
Sorry, didnt answer right to the question.

Table created like this :

CREATE TABLE  `test`.`t5` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `size` enum('S','M','L','XL') NOT NULL,
  PRIMARY KEY  USING BTREE (`id`,`size`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Same data inserted, then the query :

SELECT id,MAX(size) FROM `t5`
GROUP BY id
ORDER BY id,size

=>

1, 'L'
2, 'XL'
3, 'L'
4, 'L'
5, 'XL'

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mock5cAuthor Commented:
When you're creating the table, test.t5, id is the primary key.  I will have cases where id is repeated because it has multiple sizes (see my sample data above).  I want the query to extract the row of the greatest size for each id.


0
flob9Commented:
Doesnt matter if id is primary or not. The point is having size ENUM, with ordered items.
0
flob9Commented:
by default, enum is considered as numeric index, but if you compare it with string, the value is evaluated :

SELECT * FROM `t5` where size = 1;
1, 'S'
5, 'S'

SELECT * FROM `t5` where size = 'XL';

2, 'XL'
5, 'XL'
0
SharathData EngineerCommented:
try this
select t1.id,t1.size 
  from (select *,case size when 'S' then 1 when 'M' then 2 when 'L' then 3 when 'XL' then 4 end as sizeN from YourTable)t1 
  join (select id,max(case size when 'S' then 1 when 'M' then 2 when 'L' then 3 when 'XL' then 4 end) as sizeN from YourTable group by id) t2
    on t1.id = t2.id and t1.sizeN = t2.sizeN

Open in new window

0
SharathData EngineerCommented:
you can also try this
select * from YourTable t1
where case size when 'S' then 1 when 'M' then 2 when 'L' then 3 when 'XL' then 4 end = 
(select max(case size when 'S' then 1 when 'M' then 2 when 'L' then 3 when 'XL' then 4 end) from YourTable t2 where t1.id = t2.id)

Open in new window

0
racekCommented:
select id, RIGHT(MAX(IF(size='XL','4XL',IF(size='L','3L ',IF(size='M','2M ','1S ')))),0) size
 from YourTable
GROUP BY id;
0
racekCommented:
... and if you don't like the ' ' (space) after L.M.S , you can use :

select id, TRIM(RIGHT(MAX(IF(size='XL','4XL',IF(size='L','3L ',IF(size='M','2M ','1S ')))),0))  max_size
 from YourTable
GROUP BY id;
0
Ravi KallaTechnology LeadCommented:
attached code is the exact solution for this problem

select id, decode(max(decode(size1,'XL',4,'L',3,'M',2,'S',1,0)),4,'XL',3,'L',2,'M',1,'S','INVALID') from temp group by id;

Open in new window

0
Ravi KallaTechnology LeadCommented:
sorry for the typo error in the column name. Assuming that the table that contains the columns "id" and "size" is "temp", i have written the following query.

select id,
  decode(
       max(
         decode(size,'XL',4,'L',3,'M',2,'S',1,0)
          ),4,'XL',3,'L',2,'M',1,'S','INVALID'
        ) from temp group by id;  

Open in new window

0
earth man2Commented:
decode is Oracle syntax if you need postgresql equivalent then you can use case construct.
eg
select id,
case size when 5 then 'XL' when 4 then 'L' when 3 then 'M' when 2 then 'S' else 'UNKNOWN' end from(
select id,
max( case size when 'XL' then 5 when 'L' then 4 when 'M' then 3 when 'S' then 2  else 0 end ) as size from your_table_of_data group by id ) foo;
0
racekCommented:
it should be right 2 ...



select id, RIGHT(MAX(
         CASE WHEN size='XL' THEN '4XL' 
              WHEN size='L' THEN ELSE '3L ' 
              WHEN size='M' THEN '2M ' 
              ELSE '1S ' END),2) as size
 from YourTable
GROUP BY id;

Open in new window

0
earth man2Commented:
RIGHT is not a standard function in PostgreSQL use substr( X from 2) instead.
select id, substr(MAX(
         CASE WHEN size='XL' THEN '4XL'
              WHEN size='L' THEN ELSE '3L '
              WHEN size='M' THEN '2M '
              ELSE '1S ' END) from 2 ) as size
 from YourTable
GROUP BY id;
0
earth man2Commented:
thats substring not substr !
testdb=> select id, substring(MAX(
         CASE WHEN size='XL' THEN '4XL'
              WHEN size='L' THEN '3L '
              WHEN size='M' THEN '2M '              ELSE '1S ' END) from 2 ) as size
 from Your_Table_of_data
GROUP BY id;
  id  | size
------+------
 0001 | M
 0002 | XL
(2 rows)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
burtsevCommented:
I think you may want 2 different cases
1. get exactly what you ask
2. get full rows for the conditions you have described

PostgreSQL syntax

Data may be defined as follows:
create type sizes as enum('S', 'M', 'L', 'XL');

create table clo(id varchar(10), size varchar(5));

insert into clo values
('0001', 'S'),('0001', 'M'),('0002', 'L'),
('0002', 'XL'),('0003', 'L'),('0004', 'L'),
('0005', 'S'),('0005', 'M'),('0005', 'XL');

-----------------------------------------
1. To query exactly what you need you may write the foloowing

select id, max(size::sizes) as size from clo
group by id
order by id;


2. To query full rows that with max sizes use the following (8.4 version only)

select * from
(
   select
      *,
      max(size::sizes) over w as max
  from clo
   window w as (partition by id order by size::sizes
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) t
where max = size::sizes
order by id;

0
burtsevCommented:
for the 8.3 "full row solution" may be reached as

select clo.* from clo join (
select id, max(size::sizes) as size from clo
group by id
order by id
) max_clo on max_clo.id = clo.id and max_clo.size = clo.size::sizes;


however, it uses hash join and so it is not quick as previouse.
0
mock5cAuthor Commented:
All suggestions have been excellent so far.

I have looked at how I will actually use this data and I will actually need to return the ID in the tableB that is in the same row as the size.  The example I provided is only a single table but I actually have two tables.  I thought I could get by with the example I provided but let me demonstrate my problem.

The 1st query shows the problem where the size is correct but the wrong ID is pulled from tableB.  It is always grabbing the 1st ID.  The 2nd query shows the correct size and ID.  Is this a good way to go about this problem?  I always figure there is a better approach.  I thought putting the size and id together would guarantee that I have the correct ID and then I would need to parse the size column to get that ID.

I did this in MySQL but MySQL or Postgres syntax is fine with me since I can convert between them pretty easily.  I'm using both all the time.  For this problem, it is actually done in access with a mysql (or postgres) backend.

create table TABLEA(
id varchar(4),
name varchar(12)
);
 
create table TABLEB(
table_a_id varchar(4),
id integer,
size varchar(2)
);
 
insert into tableA(id,name) values('0001','bob');
insert into tableA(id,name) values('0002','jane');
insert into tableA(id,name) values('0003','rich');
insert into tableA(id,name) values('0004','beth');
insert into tableA(id,name) values('0005','john');
 
insert into tableb(table_a_id,id,size) values ('0001',1049,'S');
insert into tableb(table_a_id,id,size) values ('0001',982,'M');
insert into tableb(table_a_id,id,size) values ('0002',1112,'L');
insert into tableb(table_a_id,id,size) values ('0002',3238,'XL');
insert into tableb(table_a_id,id,size) values ('0003',889,'L');
insert into tableb(table_a_id,id,size) values ('0004',3419,'L');
insert into tableb(table_a_id,id,size) values ('0005',3007,'S');
insert into tableb(table_a_id,id,size) values ('0005',989,'M');
insert into tableb(table_a_id,id,size) values ('0005',766,'XL');
 
# This one returns correct size but wrong TABLEB id
select b.id,
substring(MAX(
   CASE WHEN size='XL' THEN '4XL'
   WHEN size='L' THEN '3L '
   WHEN size='M' THEN '2M '
   ELSE '1S '
END) from 2 ) as size
from TABLEA a
inner join TABLEB b on a.id=b.table_a_id
group by a.id;
 
# This one returns correct size and correct TABLEB id.
# Is this a good approach?
select b.id,
substring(MAX(
   CASE
      WHEN size='XL' THEN concat('4XL',',',b.id)
      WHEN size='L' THEN concat('3L ',',',b.id)
      WHEN size='M' THEN concat('2M ',',',b.id)
      ELSE concat('1S ',',',b.id)
END),2) as size
from TABLEA a
inner join TABLEB b on a.id=b.table_a_id
group by a.id;

Open in new window

0
racekCommented:
group by b.id helps ????
select b.id,
substring(MAX(
   CASE WHEN size='XL' THEN '4XL'
   WHEN size='L' THEN '3L '
   WHEN size='M' THEN '2M '
   ELSE '1S '
END) from 2 ) as size
from TABLEA a join TABLEB b on a.id=b.table_a_id
group by b.id;

Open in new window

0
earth man2Commented:
testdb=> select split_part(size,',',2) as b_id, split_part(size,',',1) as size from (
select substring( MAX( CASE
 WHEN size='XL' THEN '4XL,'|| b.id
 WHEN size='L'  THEN '3L,' || b.id
 WHEN size='M'  THEN '2M,' || b.id
                ELSE '1S,' || b.id END), 2 ) as size from TABLEA a
inner join TABLEB b on a.id = b.table_a_id group by a.id ) as foo;
 b_id | size
------+------
 982  | M
 3238 | XL
 889  | L
 3419 | L
 766  | XL
(5 rows)

if you use postgresql 8.4 then you could use a window function.  Is split_part available in MySQL, concat is not standard in postgresql but || operator is ANSI standard string concatenation operator ?  The joys of "standard" SQL, eh.
0
flob9Commented:
If the author has not abandonned the question, I would be glad to take a closer look.

Just waiting for feedback on the last comments.
0
SharathData EngineerCommented:
angelIII - There are plenty of suggestions provided. My posts (24866082 and 24866094) will give the result what exactly the askers wants. I didn't check other's posts.
0
flob9Commented:
Ok .. I didn't understand why the very simple enum solution wasn't choosen ... if someone can explain why, i'll be glad.
 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.