Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql: Order by max N values from subquery

Tags:

I'm about to throw in the towel with this.

Preface: I want to make this work with any N, but for the sake of simplicity, I'll set N to be 3.

I've got a query (MySQL, specifically) that needs to pull in data from a table and sort based on top 3 values from that table and after that fallback to other sort criteria.

So basically I've got something like this:

SELECT tbl.id 
FROM
  tbl1 AS maintable 
  LEFT JOIN 
  tbl2 AS othertable 
  ON
  maintable.id = othertable.id
ORDER BY 
  othertable.timestamp DESC, 
  maintable.timestamp DESC

Which is all basic textbook stuff. But the issue is I need the first ORDER BY clause to only get the three biggest values in othertable.timestamp and then fallback on maintable.timestamp.

Also, doing a LIMIT 3 subquery to othertable and join it is a no go as this needs to work with an arbitrary number of WHERE conditions applied to maintable.

I was almost able to make it work with a user variable based approach like this, but it fails since it doesn't take into account ordering, so it'll take the FIRST three othertable values it finds:

ORDER BY 
  (
    IF(othertable.timestamp IS NULL, 0, 
      IF(
        (@rank:=@rank+1) > 3, null, othertable.timestamp
      )
    )
  ) DESC

(with a @rank:=0 preceding the statement)

So... any tips on this? I'm losing my mind with the problem. Another parameter I have for this is that since I'm only altering an existing (vastly complicated) query, I can't do a wrapping outer query. Also, as noted, I'm on MySQL so any solutions using the ROW_NUMBER function are unfortunately out of reach.

Thanks to all in advance.

EDIT. Here's some sample data with timestamps dumbed down to simpler integers to illustrate what I need:

maintable

id      timestamp
1       100
2       200
3       300
4       400
5       500
6       600

othertable

id     timestamp
4      250
5      350
3      550
1      700

=>

1
3
5
6
4
2

And if for whatever reason we add WHERE NOT maintable.id = 5 to the query, here's what we should get:

1
3
4
6
2

...because now 4 is among the top 3 values in othertable referring to this set.

So as you see, the row with id 4 from othertable is not included in the ordering as it's the fourth in descending order of timestamp values, thus it falls back into getting ordered by the basic timestamp.

The real world need for this is this: I've got content in "maintable" and "othertable" is basically a marker for featured content with a timestamp of "featured date". I've got a view where I'm supposed to float the last 3 featured items to the top and the rest of the list just be a reverse chronologic list.