Akina

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY list_name ORDER BY date DESC) rn
    FROM table
    )
SELECT *
FROM cte
WHERE rn = 1

SELECT t1.*
FROM table t1
NATURAL JOIN (
    SELECT list_name, MAX(date) date
    FROM table
    GROUP BY 1
    )

DELETE t1 
FROM review_detail t1
JOIN review_detail t2 USING (title, nickname)
WHERE t1.detail_id < t2.detail_id;

INSERT INTO people (last_name, first_name, sex, birthday)
VALUES ('Иванов', 'Иван', 'm', TO_DATE('1999-04-04', 'YYYY-MM-DD'));

WITH cte AS ( SELECT *, 
                     LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime, 
                     LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event 
              FROM history )
SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
FROM cte
WHERE (event, lag_event) = (2,1)
GROUP BY login;

WITH cte AS (
    SELECT 'Bob' AS name, 25 AS age UNION ALL
    SELECT 'Mark'       , 30        UNION ALL
    SELECT 'Joe'        , 35
)
SELECT test.id,
       jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.id

хотелось бы посмотреть на версию mysql 5.7

SELECT department.name as department_name, 
       employee.name
FROM employee 
JOIN department ON employee.department_id = department.id 
WHERE 2 > ( SELECT COUNT(*)
            FROM employee emp
            WHERE emp.department_id = employee.department_id
              AND emp.id < employee.id )

SELECT department.name as department_name, 
       subquery.name
FROM department
JOIN ( SELECT employee.*,
              @row_number := CASE WHEN @department = department_id
                                  THEN @row_number + 1
                                  ELSE 1
                                  END rownumber,
              @department := department_id
       FROM employee
       CROSS JOIN ( SELECT @department:=0, @row_number:=0 ) variables
       ORDER BY department_id, id ) subquery ON subquery.department_id = department.id
WHERE subquery.rownumber <= 2

я просто хочу чтобы добавился столбце id, который бы шел по порядку с той таблицы которая у меня уже есть.

WITH cte AS (
    SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
    FROM product_in_purchase AS PIP
    RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
    GROUP BY PIP.purchase_id
)
( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
UNION 
( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )

from
table1 , ( table2
           left join table1 h on table1.id = h.id
           left join table2 s on table2.id = s.id )

FROM table1 
CROSS JOIN table2
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id

FROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id