there seem to be a lot of threads here asking similar questions, but I'm yet to find the one that helps me solve my problem.
I'm trying to select a row with the lowest value where three other conditions must be true OR fall back to another set of conditions, given a variable (uc
) and select the highest price.
Table: level (sample data)
id |price |copies |used |uc
--------------------------------------
1 |7 |10 |10 |charlie01
2 |17 |10 |8 |charlie01
3 |37 |5 |5 |charlie01
4 |47 |10 |9 |charlie01
7 |5 |15 |9 |charlie02
8 |15 |15 |0 |charlie02
9 |35 |15 |0 |charlie02
10 |45 |15 |0 |charlie02
11 |3 |5 |5 |charlie03
12 |13 |5 |5 |charlie03
Results: EDITED RESULTS - charlie01 should be 2
VARIABLE |RESULTING ROW
--------------------------
charlie01 |2
charlie02 |7
charlie03 |12
### START Pseudocode: ###
Select the row with the lowest price
where used
is less than copies
and used
is greater than 0
and uc
equals VARIABLE
OR IF NO RESULTS (which means all the used
are equal to copies
) Select the row with the highest price where used
equals copies
and uc
equals VARIABLE
SELECT *
FROM level
WHERE
(
used < copies
AND used > 0
AND uc = *VARIABLE*
)
### OR IF NO RESULTS ###
(
used = copies
AND uc = *VARIABLE*
)
ORDER BY price ASC
LIMIT 1
### END Pseudocode ###
This is the query I wrote (using uc
charlie01
as an example):
SELECT *
FROM (
SELECT *
FROM `level`
WHERE
((
`used` < `copies`
AND `used` > '0'
)
OR
(
`used` = `copies`
))
AND `uc` = 'charlie01'
ORDER BY `price` ASC
) AS T
ORDER BY `used` ASC
LIMIT 1
It doesn't work on the sample data because of record 3
, even though used = copies
. This is because used on record 3 < used on record 2 (the row we want, because it has the lowest price)
I know MAX() exists and have tried for the last hour to use it, but with no success, I really don't think I understand how it works, It seems to just be selecting the maximum value of the whole column no matter what...?
Now, I've gone round in circles adding conditions and such, but I think I need to do a JOIN
of some type but I'm not really sure where to start with it. How do you think I should go about this query?
EDIT: I'm really sorry, I just realised I was saying highest price when I was actually trying to select the lowest price where used
< copies
BUT the highest price if all used
= copies
Cheers
Charlie