Get oldest row in one of the two tables

Question

So I have a fairly complex query right now that goes something like this:

SELECT keywords.id as keyword_id,
       keywords.keyword as keyword,
       i.id as position_id,
       i.position as position,
       i.created_at as created_at
FROM keywords
LEFT JOIN positions i
  ON  i.keyword_id = keywords.id
  AND i.id >= COALESCE((
    SELECT i1.id
    FROM positions i1
    WHERE i1.keyword_id = keywords.id
    ORDER BY i1.id DESC
    LIMIT 1
    OFFSET 1
  ), 0)
WHERE keywords.website_id = ?
ORDER BY keywords.keyword ASC, i.id ASC', [$this->id]);

So now I'm trying to add additional data to the query. I need the oldest row (based on created_at column) in either positions or position_backups table. I would need to check for oldest row in position_backups first and if it's not found, then get oldest row in positions for that keyword_id.

Can anyone give me any pointers in how to get this done? I've searched a lot but have not quite found what I'm looking for.

This is what I have so far. I'm not sure if it does exactly what I need as I need to test it more. Any comments or suggestions appreciated.

        SELECT keywords.id as keyword_id,
               keywords.keyword as keyword,
               i.id as position_id,
               i.position as position,
               i.url as url,
               i.created_at as created_at,
               ifnull ((select position from position_backups WHERE keyword_id = keywords.id ORDER BY id ASC LIMIT 1), (select position from positions WHERE keyword_id = keywords.id ORDER BY id ASC LIMIT 1)) as first_position
        FROM keywords
        LEFT JOIN positions i
          ON  i.keyword_id = keywords.id
          AND i.id >= COALESCE((
            SELECT i1.id
            FROM positions i1
            WHERE i1.keyword_id = keywords.id
            ORDER BY i1.id DESC
            LIMIT 1
            OFFSET 1
          ), 0)
        WHERE keywords.website_id = 20
        ORDER BY keywords.keyword ASC, i.id ASC

it's usually "most recent row" - search for that and modify the logic to use MIN rather than MAX date — Caius Jard, Dec 28 '17 at 16:41
e.g https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user — Caius Jard, Dec 28 '17 at 16:42
Your query should return top 2 rows per group. Is that what you want? — Paul Spiegel, Dec 28 '17 at 16:45
@PaulSpiegel, nope. The query I have now already returns top 2 rows per group. In addition to that, I'm trying to get 1 oldest row from one of the two tables. But I'm pretty bad at SQL especially when it comes to `IF` or `CASE` statements which I would need here. — zen, Dec 28 '17 at 16:51
So you want the latest two rows (as the actual query is doing) and a third row, which is the oldest one from the group? — Paul Spiegel, Dec 28 '17 at 17:08
Correct @PaulSpiegel. I added an `IFNULL` statement to the query which I think does what I need. Just need to test it to verify. Any comments welcome. — zen, Dec 28 '17 at 17:14
See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Dec 28 '17 at 18:20

Get oldest row in one of the two tables

0 Answers0