In a Mysql 5.5 database I have a table. Originally, I just needed the first record with the highest amount.
bid
----------------------
id int
personId int
itemId int
amount double
tieBreak boolean
updatedDate datetime
DATA:
id personId itemId amount tiebreak updatedDate
----------------------------------------------------------------------------------
1 4 111 100 1 2013-08-26 09:00:00
2 5 111 100 0 2013-08-26 10:00:00
3 6 111 100 1 2013-08-26 11:00:00
...
4 24 222 200 0 2013-08-27 09:00:00
5 57 222 200 0 2013-08-27 10:00:00
6 12 222 200 0 2013-08-27 11:00:00
...
7 2 333 400 1 2013-08-27 11:00:00
8 7 333 300 1 2013-08-27 11:00:00
9 25 333 600 1 2013-08-27 11:00:00
I need the entire record for the oldest entry for each itemId in the table where amount equals the max amount for that item and items with tieBreak true (1) get priority over items with tiebreak false (0).
id personId itemId amount tiebreak updatedDate
----------------------------------------------------------------------------------
1 4 111 100 1 2013-08-26 09:00:00
4 24 222 200 0 2013-08-27 09:00:00
9 25 333 600 1 2013-08-27 11:00:00
1 because its the oldest record with tiebreak = 1 and the highest amount 4 because its the oldest record and everything else is equal 9 because its the highest amount
The closest I got was:
SELECT a.*
FROM bid a
LEFT OUTER JOIN bid a2 ON (a.itemId = a2.itemId AND a.amount < a2.amount)
WHERE a2.id IS NULL
ORDER BY tiebreak DESC, updatedDate ASC;
Which kind of works but pulls back ALL bids matching highest amount sorted... I want ONLY the single oldest bid for each item where the highest amount and the highest value for tiebreak (1 or 0). Meaning an amount of 10 with tiebreak 1 beats out an amount of 10 with tiebreak 0 even if the second bid came first.
Your list of conditions is ideal for an order by clause. Alas, you cannot select the first item for an item easily in MySQL.
But, there is a trick -- assuming that id uniquely identifies each row (and shame on you if such a column name doesn't have that property). You can use group_concat() with order by to order the ids by the conditions. Then, substring_index() can get the first one. Voila! Almost as if MySQL supported row_number() (the ANSI standard way to do all of this).
Here is what the query looks like:
select bid.*
from (select itemid,
substring_index(group_concat(id order by amount desc, updateddate asc, tiebreak desc), ',', 1) as id
from bid
group by itemid
) b join
bid
on b.itemid = bid.itemid and b.id = bid.id;