0

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
Rick James
  • 135,179
  • 13
  • 127
  • 222
Nintinugga
  • 3
  • 2

2 Answers2

0

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)";
drtechno
  • 298
  • 2
  • 9
0

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
  • Thanks for the answer mr. ultimate8. Sorry for replying this late to your answer, I had no time to work(I'm a working student and was busy with university stuff for the past weeks). Your answer is nearly right, "AND A.col3 = B.MAX_col3 AND A.col4 = B.MIN_col4" will give no results. If you change it to AND (A.col3 = B.MAX_col3 OR A.col4 = B.MIN_col4) I will accept your answer. – Nintinugga Jul 13 '17 at 11:05