Postgresql: compare record against sibling, but limit search only to active records
Your best (or simplest) choice may be using DISTINCT ON:

WITH latest_prices AS (
  SELECT DISTINCT ON (price_sheet_num, item_id) *
  FROM prices
  ORDER BY price_sheet_num, item_id, created_at DESC
  )
SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost &gt; lpr.cost;


Ensure first there's an index for prices ON (price_sheet_num, item_id, created_at), or better still, prices ON (price_sheet_num, item_id, created_at DESC);

If this doesn't preform well enough, you may benefit from creating a temporary table:

CREATE TEMP TABLE latest_prices (LIKE prices) ON COMMIT DROP;
INSERT INTO latest_prices
SELECT DISTINCT ON (price_sheet_num, item_id) *
FROM prices
ORDER BY price_sheet_num, item_id, created_at DESC;
CREATE UNIQUE INDEX ON latest_prices (price_sheet_num, item_id);

SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost &gt; lpr.cost;