Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

Some SQL things

:ID: E5F844B5-1C69-4CD6-B0D8-C785C776CB5C

# Join only on first record

# Correlated subquery

This loops since the subquery runs once for each record in the outer query

select * from users
  join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1)

# Complete subquery

# PostgreSQL DISTINCT ON with different ORDER BY

select * from users
  join (
    select distinct on (user_id) *
      from widgets
      order by user_id, created_at desc)
    as most_recent_user_widgeton on users.id = most_recent_user_widget.user_id

# If not using PostgreSQL (ie DISTINCT ON)

We can use the ROW_NUMBER function with ordering and selecting the first row.

See also https://stackoverflow.com/a/49980276

select * from users
  join (
    select * from (
        select *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at desc) rn
        from widgets) w where w.rn = 1
      ) as most_recent_user_widgeton on users.id = most_recent_user_widget.user_id

# Date / Time stuff

# Some time ago

  • PG: NOW() - '1 day'::INTERVAL
  • MSSQL: DATEADD(day, -1, GETDATE())

# Convert Datetime to Date

  • MSSQL
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Search Results