0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for science fiction and fantasy enthusiasts

SELECT
(SELECT TOP 1
    LEN(e.Text) 
    FROM PostHistory AS e 
    WHERE a.Id = e.PostId AND e.PostHistoryTypeId IN (2,5,8)
    ORDER BY e.id DESC) 
    AS ale,
LEN(a.Body) AS alh,
'site://a/' + CAST(a.Id AS NVARCHAR) + '/' + CAST(m.Id AS NVARCHAR) + '|' + q.Title AS atl,
q.tags AS qta,
COALESCE('site://users/' + CAST(a.OwnerUserId AS NVARCHAR) + '|' + u.DisplayName, a.OwnerDisplayName) AS uli,
a.CreationDate AS ada,
a.Id AS aid
FROM Posts AS a
JOIN Posts AS q ON a.ParentId = q.Id
LEFT JOIN users AS u ON u.Id = a.OwnerUserId
LEFT JOIN Users AS m ON m.AccountId = 1192385
WHERE 2 = a.PostTypeId
AND 16384 <= LEN(a.Body)
ORDER BY ale DESC;

fork query

edited oct 1 19

created dec 21 18 b_jonas