Left join LATERAL

SQL

lateral goes through each line in the 'from' table and queries the code in the lateral code

select 
       c.id category_id,
       c.category category,
       p.title title,
       p.views as views,
       p.id post_id
from categories c
  left join lateral (
    select id, category_id, title,views
    from posts
    where c.id=category_id
    order by views desc, id asc
    limit 2) p on c.id=p.category_id
order by 2, 4 desc, 5
changjulian17
Did you find this snippet useful?

Sign up for free to to add this to your code library

Getting the Last Record Using Rank Function - T-SQL Example
SQL

T-SQL

50
Getting the Date for the Previous Saturday with T-SQL
SQL

T-SQL

42
Remove Leading Zeros from a Field with T-SQL
SQL

T-SQL | Data Cleaning

17
SQL generate_series, dayofweek(DOW), weekday, functions
SQL

12
median
SQL

11
SQL window function
SQL

10
SQL RANK
SQL

10
EXISTS
SQL

10
SQL creating a view
SQL

10
SQL window function II: percent growth
SQL

10