Solved

query to grab great value in column for each id

Posted on 2009-07-15
25
285 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:mock5c
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +4
25 Comments
 
LVL 14

Expert Comment

by:flob9
ID: 24864379
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
 
LVL 14

Expert Comment

by:flob9
ID: 24864410
(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
 
LVL 14

Expert Comment

by:flob9
ID: 24864458
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mock5c
ID: 24864546
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
 
LVL 14

Expert Comment

by:flob9
ID: 24864750
Doesnt matter if id is primary or not. The point is having size ENUM, with ordered items.
0
 
LVL 14

Expert Comment

by:flob9
ID: 24864772
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 75 total points
ID: 24866082
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24866094
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
 
LVL 14

Expert Comment

by:racek
ID: 24866618
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
 
LVL 14

Expert Comment

by:racek
ID: 24866631
... 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
 
LVL 6

Expert Comment

by:Ravi Kalla
ID: 24866707
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
 
LVL 6

Expert Comment

by:Ravi Kalla
ID: 24866727
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
 
LVL 22

Expert Comment

by:earth man2
ID: 24884095
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
 
LVL 14

Expert Comment

by:racek
ID: 24885230
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
 
LVL 22

Expert Comment

by:earth man2
ID: 24888768
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 175 total points
ID: 24901841
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
 

Expert Comment

by:burtsev
ID: 24902043
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
 

Expert Comment

by:burtsev
ID: 24902190
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
 

Author Comment

by:mock5c
ID: 24921371
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
 
LVL 14

Expert Comment

by:racek
ID: 24922043
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
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 175 total points
ID: 24922441
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
 
LVL 14

Expert Comment

by:flob9
ID: 25093451
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
 
LVL 41

Expert Comment

by:Sharath
ID: 25104510
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
 
LVL 14

Expert Comment

by:flob9
ID: 25119236
Ok .. I didn't understand why the very simple enum solution wasn't choosen ... if someone can explain why, i'll be glad.
 
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question