get max value of non duplicated records

user_18980271 0
Viewed 41

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.

mysql sql php
1 Answers

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

user_51935360
Related