Mysql: Order by max N values from subquery

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.

---

也许是这样的。

SELECT
  id
FROM
  (SELECT 
    tbl.id,
    CASE WHEN othertable.timestamp IS NULL THEN 
      0 
    ELSE 
      @i := @i + 1
    END AS num,
    othertable.timestamp as othertimestamp,
    maintable.timestamp as maintimestamp
  FROM
    tbl1 AS maintable
    CROSS JOIN (select @i := 0) i 
    LEFT JOIN tbl2 AS othertable 
      ON maintable.id = othertable.id
  ORDER BY
    othertable.timestamp DESC) t
ORDER BY
  CASE WHEN num > 0 AND num <= 3 THEN
    othertimestamp
  ELSE
    maintimestamp
  END DESC

---

Modified answer:

select ilv.* from
(select sq.*, @i:=@i+1 rn from
 (select @i := 0) i
  CROSS JOIN 
 (select m.*, o.id o_id, o.timestamp o_t
  from maintable m
  left join othertable o
  on m.id = o.id
  where 1=1
  order by o.timestamp desc) sq
) ilv
order by case when o_t is not null and rn <=3 then rn else 4 end,
         timestamp desc

SQLFiddle here.

Amend where 1=1 condition inside subquery sq to match required complex selection conditions, and add appropriate limit criteria after the final order by for paging requirements.

---

Can you use a union query as below?

(SELECT id,timestamp,1 AS isFeatured FROM tbl2 ORDER BY timestamp DESC LIMIT 3)
UNION ALL
(SELECT id,timestamp,2 AS isFeatured FROM tbl1 WHERE NOT id in (SELECT id from tbl2 ORDER BY timestamp DESC LIMIT 3))
ORDER BY isFeatured,timestamp DESC

This might be somewhat redundant, but it is semantically closer to the question you are asking. This would also allow you to parameterize the number of featured results you want to return.

链接地址: http://www.djcxy.com/p/14900.html

上一篇: 是否有可能有一个单独的节点

下一篇: Mysql：按子查询的最大N值排序