Solved

query to grab great value in column for each id

Posted on 2009-07-15
25
283 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
reverse engineer .sql from php files 11 49
How to structure query with count aggregate 4 41
show child records separated by commas 12 33
SQL Syntax 6 36
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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