Get whole dataset for each unique combination of two columns and their max and min values

0
votes

I'm desperately searching a query which gives me all datasets to the following query: 

SELECT col1, col2, MAX(col3), MIN(col4) FROM table1 GROUP BY col1, col2

This returns a Table like the following

col 1  | col2   | max(col3) | min(col4)
------ | ------ | --------- | ------ 
10     | 0.5    | 20        | -5
10     | 0.9    | 30        | -2

What I need is the dataset(the whole row) for each max and min and each entity. I could do this programatically with a query like 

SELECT * FROM table1 WHERE col1 = 10 AND (col2 = 0.5 OR col2 = 0.9) AND ((col3 = 20 OR col4 = -5) OR (col3 = 30 OR col4 = -2 ))

which returns me 4 datasets,

but the Query gets more ugly with each added MAX or MIN or GROUP BY(this is what the users do with the program).

Let me try to explain it in one sentence: I'm searching a way of displaying the dataset to each max and min value(not col1,col2) of each unique combination of values of col1 and col2.

Is there a way, to do this in one query?

If you need further details don't fear to comment.

Edit 1

Dataset

id   | col 1  | col2   | col3   | col4
---- | ------ | ------ | ------ | ------ 
  0  |   10   |  0.5   |  20    |  0
  1  |   10   |  0.5   |  15    | -1
  2  |   10   |  0.5   |  17    | 12
  3  |   10   |  0.5   |  11    |  3
  4  |   10   |  0.5   |   5    | -5
  5  |   10   |  0.9   |  10    |  1
  6  |   10   |  0.9   |  22    | -1
  7  |   10   |  0.9   |  15    | -2
  8  |   10   |  0.9   |  30    |  3

Desired Output

id   |  col 1  | col2   | col3   | col4
---- |  ------ | ------ | ------ | ------ 
  0  |   10    |  0.5   |  20    |  0
  4  |   10    |  0.5   |  5     | -5
  7  |   10    |  0.9   |  15    | -2
  8  |   10    |  0.9   |  30    |  3
2
mysqlsqlmariadbgreatest-n-per-group
Please post the sample data set and the desired output. – peterm
It's messy to do "groupwise max". See the tag I added. – Rick James
@RickJames will look into those questions, appreciate your input – Nintinugga

2 Answers

0
votes

This should do it: 

SELECT A.* FROM

table1 A

INNER JOIN

(SELECT 
col1, 
col2, 
MAX(col3) AS MAX_col3, 
MIN(col4) AS MIN_col4 
FROM table1 
GROUP BY 
col1, 
col2) B

ON A.col1 = B.col1
AND A.col2 = B.col2
AND A.col3 = B.MAX_col3
AND A.col4 = B.MIN_col4
0
votes

if you know your two column values: this would be the dataset for when col3 is maximum and col 4 minimum at those two colum values. keep in mind your constraint to the query is the two column values. compound query should be like:

 select * from table1 as A where col1 = '$value1' and col2 = '$value2' and    col3=(select MAX(col3) FROM table1 as B where A.col1 = B.col1 and A.col2 = B.col2) and col4=(select MIN(col4) FROM table1 as C where A.col1 = C.col1 and A.col2 = C.col2)";