Get multiple GROUP BY results per group, or use separate concatenated table

Question

I am working on an auction web application. Now i have a table with bids, and from this table i want to select the last 10 bids per auction.

Now I know I can get the last bid by using something like:

SELECT bids.id FROM bids WHERE * GROUP BY bids.id ORDER BY bids.created

Now I have read that setting an amount for the GROUP BY results is not an easy thing to do, actually I have found no easy solution, if there is i would like to hear that.

But i have come up with some solutions to tackle this problem, but I am not sure if i am doing this well.

Alternative

The first thing is creating a new table, calling this bids_history. In this table i store a string of the last items.

example:

bids_history
================================================================
auction_id   bid_id     bidders               times
    1        20,25,40   user1,user2,user1     time1,time2,time3

I have to store the names and the times too, because I have found no easy way of taking the string used in bid_id(20,25,40), and just using this in a join. This way i can just just join on auction id, and i have the latest result.

Now when there is placed a new bid, these are the steps:

• insert bid into bids get the lastinserteid
• get the bids_history string for this auction product
  • explode the string
  • insert new values
  • check if there are more than 3
  • implode the array, and insert the string again

This all seems to me not a very well solution. I really don't know which way to go. Please keep in mind this is a website with a lot of bidding's, they can g up to 15.000 bidding's per auction item. Maybe because of this amount is GROUPING and ORDERING not a good way to go. Please correct me if I am wrong.

After the auction is over i do clean up the bids table, removing all the bids, and store them in a separate table.

Can someone please help me tackle this problem!

And if you have been, thanks for reading..

EDIT

The tables i use are:

bids
======================
id      (prim_key)
aid     (auction id)
uid     (user id)
cbid    (current bid)
created (time created)
======================

auction_products
====================
id        (prim_key)
pid       (product id)
closetime (time the auction closses)

What i want as the result of the query:

result
===============================================
auction_products.id   bids.uid   bids.created
2                        6         time1
2                        8         time2
2                        10        time3
5                        3         time1
5                        4         time2
5                        9         time3
7                        3         time1
7                        2         time2
7                        1         time3

So that is per auction the latest bids, to choose by number, 3 or 10

Answer 1

To select last 10 bids for a given auction, just create a normalized bids table (1 record per bid) and issue this query:

SELECT  bids.id
FROM    bids
WHERE   auction = ?
ORDER BY
        bids.created DESC
LIMIT 10

To select last 10 bids per multiple auctions, use this:

SELECT  bo.*
FROM    (
        SELECT  a.id,
                COALESCE(
                (
                SELECT  bi.created
                FROM    bids bi
                WHERE   bi.auction = a.id
                ORDER BY
                        bi.auction DESC, bi.created DESC, bi.id DESC
                LIMIT 1 OFFSET 9
                ), '01.01.1900'
                ) AS mcreated
                COALESCE(
                (
                SELECT  bi.id
                FROM    bids bi
                WHERE   bi.auction = a.id
                ORDER BY
                        bi.auction DESC, bi.created DESC, bi.id DESC
                LIMIT 1 OFFSET 9
                ), 0)
                AS mid
        FROM    auctions a
        ) q
JOIN    bids bo
ON      bo.auction >= q.auction
        AND bo.auction <= q.auction
        AND (bo.created, bo.id) >= (q.mcreated, q.mid)

Create a composite index on bids (auction, created, id) for this to work fast.

Answer 2

EDIT: This is wrong :-)

You will need to use a subquery:

SELECT bids1.id
FROM ( SELECT *
       FROM bids AS bids1 LEFT JOIN
            bids AS bids2 ON bids1.created < bids2.created
                          AND bids1.AuctionId = bids2.AuctionId
       WHERE bid2.id IS NULL)
ORDER BY bids.created DESC
LIMIT 10

So the subquery performs a left join from bids to itself, pairing each record with all records that have the same auctionId and and a created date that is after its own created date. For the most recent record, there will be no other record with a greater created date, and so that record would not be included in the join, but since we use a Left join, it will be included, with all the bids2 fields being null, hence the WHERE bid2.id IS NULL statement.

So the sub query has one row per auction, contianing the data from the most recent bid. Then simply select off the top ten using orderby and limit.

If your database engine doesn't support subqueries, you can use a view just as well.

Answer 3

Why do this in one query?

$sql = "SELECT id FROM auctions ORDER BY created DESC LIMIT 10";
$auctions = array();

while($row = mysql_fetch_assoc(mysql_query($sql)))
  $auctions[] = $row['id'];

$auctions = implode(', ', $auctions);
$sql = "SELECT id FROM bids WHERE auction_id IN ($auctions) ORDER BY created LIMIT 10";
// ...

You should obviously handle the case where, e.g. $auctions is empty, but I think this should work.

Answer 4

Ok, this one should work:

SELECT bids1.id
FROM bids AS bids1 LEFT JOIN
     bids AS bids2 ON bids1.created < bids2.created
                   AND bids1.AuctionId = bids2.AuctionId
GROUP BY bids1.auctionId, bids1.created
HAVING COUNT(bids2.created) < 9

So, like before, left join bids with itself so we can compare each bid with all the others. Then, group it first by auction (we want the last ten bids per auction) and then by created. Because the left join pairs each bid with all previous bids, we can then count the number of bids2.created per group, which will give us the number of bids occurring before that bid. If this count is < 9 (because the first will have count == 0, it is zero indexed) it is one of the ten most recent bids, and we want to select it.

Answer 5

Using user variable, and control flow, i end up with that (just replace the <=3 with <=10 if you want the ten auctions) :

SELECT a.*
FROM
 (SELECT aid, uid, created FROM bids ORDER BY aid, created DESC) a,
 (SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.aid THEN
   CASE WHEN @prev:=a.aid THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 3