Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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?
0
mock5c
Asked:
mock5c
  • 7
  • 4
  • 4
  • +4
3 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 KallaCommented:
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 KallaCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now