I have relation A (reviewer_id, book_id, trivalFields) Suppose at only 1 or 2 reviews exist for the book_id. I would like a result set that review_id (first), review_id (second), book_id. Example:

  johnson, smith, 'horton hears a who' 
  johnson, smith, 'green eggs and ham'.

But not

  johnson, smith, 'horton hears a who' 
  smith, johnson,  'horton hears a who' 
  johnson, smith, 'green eggs and ham'.
  smith, johnson,  'green eggs and ham'.

I know how do something like this with brute strength code - but how to do it in pure SQL??

PostgreSQL - by the way.

SELECT t1.reviewer_id AS first, 
       t2.reviewer_id AS second, 
FROM table AS t1 
LEFT JOIN table AS t2 ON t2.book_id = t1.book_id 
                     AND t2.reviewer_id > t1.reviewer_id


SELECT MIN(reviewer_id) AS first, 
       CASE WHEN COUNT(reviewer_id) > 1 
            THEN MAX(reviewer_id) 
            ELSE NULL
            END AS second, 
FROM table
GROUP BY book_id
  • Brilliant !!!!! – terary Oct 15 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.