-1

I cannot join tables setup, p and sps. I only want to join the row from sps with the newest timestamp in the created_at column.

I tried this answer: https://stackoverflow.com/a/45018194/6535278 Without any success like so:

SELECT *
FROM setup s
JOIN (SELECT p_id AS p_id_1,
        p_setup_id AS p_setup_id_1
    FROM part
    ) p
ON s.s_id = p.p_setup_id_1 
JOIN (SELECT DISTINCT ON (s_p_s_id) s_p_s_id AS s_p_s_id_1,
        part_id AS part_id_1,
        s_setup_id AS s_setup_id_1,
        created_at AS created_at_1
    FROM setup_part_setting
    ORDER BY s_p_s_id, created_at
    ) sps
ON s.s_id = sps.s_setup_id_1
AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;

My code:

SELECT *
FROM setup s
JOIN (SELECT p_id AS p_id_1,
    p_setup_id AS p_setup_id_1
    FROM part) p
ON s.s_id = p.p_setup_id_1 
JOIN (SELECT s_p_s_id AS s_p_s_id_1,
    part_id AS part_id_1,
    s_setup_id AS s_setup_id_1,
    created_at AS created_at_1
    FROM setup_part_setting) sps
ON s.s_id = sps.s_setup_id_1
AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;
philipxy
  • 14,867
  • 6
  • 39
  • 83
Anton Hoerl
  • 189
  • 1
  • 10
  • 2
    What was the problem with the solution you tried? There are many solutions in the question you linked. Without a schema and example data it's hard to help you, as you have to reverse engineer a non-working query to figure out yow to apply a particular solution. – Allan Wind Sep 06 '22 at 07:56
  • you never used desc in you query for created_at – Sund'er Sep 06 '22 at 07:59
  • Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. [ask] [Help] PS It is not helpful to just link or say you searched. Quote & explain relevant results. – philipxy Sep 06 '22 at 15:06
  • [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) – philipxy Sep 06 '22 at 15:07
  • 1
    Your goal is a faq. Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Sep 06 '22 at 15:08
  • ORDER BY without TOP/LIMIT not at the outermost level has no effect. Tables have no order; result sets can have order. – philipxy Sep 06 '22 at 15:10

1 Answers1

1

Feel free to sort subselection before to JOIN. Or even limit it:

SELECT *
FROM setup s
JOIN (
    SELECT p_id AS p_id_1, p_setup_id AS p_setup_id_1
    FROM part
) p ON s.s_id = p.p_setup_id_1 
JOIN (
    SELECT 
        s_p_s_id AS s_p_s_id_1, 
        part_id AS part_id_1,
        s_setup_id AS s_setup_id_1,
        created_at AS created_at_1
    FROM setup_part_setting
    ORDER BY created_at DESC
    LIMIT 1
) sps ON 
    s.s_id = sps.s_setup_id_1
    AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;

You can simplify your query a lot if you will use JOIN LEFT instead of joined selects. Just compare your query with the same:

SELECT s.*, p.p_id, p.p_setup_id, sps.s_p_s_id, sps.part_id, sps.s_setup_id, sps.created_at 
FROM setup s
LEFT JOIN part p ON s.s_id = p.p_setup_id
LEFT JOIN setup_part_setting sps ON s.s_id = sps.s_setup_id AND p.p_id = sps.part_id
WHERE s.s_id IS NOT NULL
ORDER BY sps.created_at DESC, s.created_at ASC;
Eugene Kaurov
  • 2,356
  • 28
  • 39