Reputation: 33
Is it possible this? I have the following table:
Col1 | Col2 | col3 |
---|---|---|
code1 | US | 9 |
code1 | 0 | 5 |
code2 | US | 4 |
code2 | 0 | 11 |
code3 | 0 | 11 |
and I'm trying to get the higher col3 value filtering by col1 and col2 my attempt right now is:
SELECT MAX(col3), col2, col1, count(col1) FROM `mytable` WHERE (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0')) GROUP BY col1;
and my result:
Col1 | Col2 | col3 | count(col1) |
---|---|---|---|
code1 | US | 9 | 2 |
code2 | 0 | 11 | 2 |
But what I need is if both codes have a col2 with value equal to 'US' then return the higher col3 value from those and ignore the ones with '0'
Col1 | Col2 | col3 | count(col1) |
---|---|---|---|
code1 | US | 9 | 2 |
or if one of those codes only have one row with col2 equal to '0' then compare between the code1 = 'US' and the code2 = '0'
Col1 | Col2 | col3 |
---|---|---|
code1 | US | 9 |
code1 | 0 | 5 |
code2 | 0 | 11 |
code3 | 0 | 11 |
giving the result:
Col1 | Col2 | col3 | count(col1) |
---|---|---|---|
code2 | 0 | 11 | 2 |
Is it possible to achieve this with a mysql query? or should I use php?
Thank you in advance.
Upvotes: 3
Views: 47
Reputation: 49410
You have very specific where clause, so you need to repeat them in teh subselect as in the Main Select
As your needs get more and more omplicated, you must increase the number of subselect to meet your requereiments, the ORDER BYs determine the row that is selected.
Followimg gets you your result, as you add more and more obsticles you must do it also in the code
CREATE TABLE tab1 ( `Col1` VARCHAR(5), `Col2` VARCHAR(2), `col3` INTEGER ); INSERT INTO tab1 (`Col1`, `Col2`, `col3`) VALUES ('code1', 'US', '9'), ('code1', '0', '5'), ('code2', 'US', '4'), ('code2', '0', '11'), ('code3', '0', '11');
SELECT * FROM tab1 WHERE col3 = (SELECT MAX(col3) FROM tab1 WHERE (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0'))) AND (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0')) ORDER BY col1,col2 DESC LIMIT 1
Col1 | Col2 | col3 :---- | :--- | ---: code2 | 0 | 11
SELECT col1,col2,col3 FROM (SELECT `Col1`, `Col2`, `col3`, ROW_NUMBER() OVER(ORDER BY col2 DESC,col3 DESC) rn2 FROM( SELECT * ,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3 DESC) rn FROM tab1) t1 WHERE rn = 1) t2 WHERE rn2 = 1
col1 | col2 | col3 :---- | :--- | ---: code1 | US | 9
CREATE TABLE tab2 ( `Col1` VARCHAR(5), `Col2` VARCHAR(2), `col3` INTEGER ); INSERT INTO tab2 (`Col1`, `Col2`, `col3`) VALUES ('code1', '0', '5'), ('code2', 'US', '4'), ('code2', '0', '11'), ('code3', '0', '11');
SELECT col1,col2,col3 FROM (SELECT `Col1`, `Col2`, `col3`, ROW_NUMBER() OVER(ORDER BY col2 DESC,col3 DESC) rn2 FROM( SELECT * ,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3 DESC) rn FROM tab2) t1 WHERE rn = 1) t2 WHERE rn2 = 1
col1 | col2 | col3 :---- | :--- | ---: code2 | 0 | 11
db<>fiddle here
Upvotes: 1