Solved

query to grab great value in column for each id

Posted on 2009-07-15
25
279 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
Comment Utility
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
Comment Utility
(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
Comment Utility
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
 

Author Comment

by:mock5c
Comment Utility
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
Comment Utility
Doesnt matter if id is primary or not. The point is having size ENUM, with ordered items.
0
 
LVL 14

Expert Comment

by:flob9
Comment Utility
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 40

Assisted Solution

by:Sharath
Sharath earned 75 total points
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
... 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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 22

Expert Comment

by:earth man2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now