a bit complicated select query

[MMDE]

I think the query would explain it better than I would ever be able to:

SELECT * FROM table WHERE col1 <= value GROUP BY col2 ORDER BY col1 DESC

 

I would almost think that worked the way I want to, but it does not...

 

What I want is to select one row per unique value in col2, which is easy.

I also want it to only select rows where col1 is equal and greater than a value (variable), which also is easy and works just fine.

The problem and what I can't seem to do is to make it only select the rows with the highest col1 values for each of the unique values in col2...

 

Can anyone help me please?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

[MMDE]

problem then is that I also want col1 to be less than a dynamic variable...

 

with other words, the max of col1 that is less than the dynamic variable... o.O

 

but perhaps left join would work... hmm

[PFMaBiSmAd]

You would just include the condition in the WHERE clause so that only the rows you are interested in are selected.

[MMDE]

hmm, sorry, but I still can't seem to figure it out >_<

I will give a graphical example:

 

table:

| col1 | col2 |

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 1 | 4 |

| 1 | 5 |

| 2 | 1 |

| 2 | 2 |

| 2 | 3 |

| 3 | 5 |

| 4 | 1 |

| 4 | 2 |

| 5 | 1 |

| 5 | 3 |

| 6 | 1 |

| 6 | 3 |

| 6 | 4 |

| 6 | 5 |

| 7 | 2 |

 

 

I got a variable, let's say it is 4.

I want to select data from rows that are equal or lower than the variable.

 

So no rows with col1 values over 4 should be selected!

 

I also want to be a bit more specific about what data that I want.

I don't want any duped col2 values, but I don't just want it to filter away rows that I might be interested in.

I want it to select the row with the highest col1 value, but remember it must be equal or less than 4.

 

 

The result of this query should be:

 

| col1 | col2 |

| 4 | 1 |

| 4 | 2 |

| 2 | 3 |

| 1 | 4 |

| 3 | 5 |

 

I don't really are if it is sorted by col2, but as long as it gives these rows back...

[MMDE]

if anyone wonders, this solved my problem:

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html