Intercalating results

[dhulke]

Hi!

So I have a pretty huge database of clients and each client has a number of objects. When people want to search for these objects on my website, I'd like to show the results intercalated by client. As of now, I'm getting like 5 rows of client A and then another 3 of client B and so forth. I'd like to get something like: First object of client A, First object of client B, First object of client C, Second object of client A, Second object of client B... and so on.

 

Since my results are paginated and there are a freking whole lot of them, I can't do it programmatically at runtime, not only for speed's sake, but because I could easily run out of memory. The way I've been doing it is to create a field "order" on my object table and every night I run a script that orders the objects the way I want. Is there a better way of doing this?

 

Thanks a lot.

[Psycho]

You didn't state whether all clients have at least one object and, if they don't, whether you want clients without any objects to be displayed. If all clients have objects OR if you don't want to show ones that don't ahve any objects, remove the "LEFT" part of the JOIN. This is just an example since I don't know your table structure. It will get one object for each client based on the object with the lowest ID.

 

This would select only the first object

 

 

SELECT *
FROM clients
LEFT JOIN objects
    ON clients.client_id = objects.client_id
    AND objects.object_id IN
    (
        SELECT MIN(object_id)
        FROM objects
        GROUP BY client_id
    )