Get min and max row from table

Question

From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date)

How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, page_id) and also the value of created_at when min(lines) and the same for max value.

Dummy data:

type_id, book_id, title_id, page_id, lines, created_at
1, 1, 1, 1, 12, 2015-04-01
1, 1, 1, 1, 10, 2015-03-01
1, 1, 1, 1, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01
1, 1, 1, 2, 5, 2015-03-01
1, 1, 1, 2, 6, 2015-03-04

Expected output:

type_id, book_id, title_id, page_id, min, min_created_at, max, max_created_at
1, 1, 1, 1, 10, 2015-03-01, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01, 6, 2015-03-04

Answer 1

One way to do this is using window functions:

SELECT DISTINCT
    type_id, book_id, title_id, page_id,
    first_value(lines)      OVER wmin AS min_lines, 
    first_value(created_at) OVER wmin AS min_created_at, 
    first_value(lines)      OVER wmax AS max_lines,
    first_value(created_at) OVER wmax AS max_created_at
FROM 
    tableX
WINDOW 
    wmin AS (PARTITION BY type_id, book_id, title_id, page_id 
             ORDER BY lines ASC, created_at ASC), 
    wmax AS (PARTITION BY type_id, book_id, title_id, page_id 
             ORDER BY lines DESC, created_at ASC) ;

Tested at SQLfiddle.

Answer 2

if i got you right, what you want is something like this:

SELECT * FROM (SELECT * FROM whatever ORDER BY type_id, book_id, title_id, page_id LIMIT 1) AS x
UNION ALL
SELECT * FROM ( SELECT * FROM whatever ORDER BY type_id, book_id, title_id, page_id DESC LIMIT 1 ) AS x;

It returns the row with the most whatever and the row with the least whatever

Answer 3

DISCLAIMER: This is not exactly what OP would want (See the accepted answer for that), but may come in handy for people landing on this question.

I stumbled across an tiny optimisation I needed to do and couldn't use WINDOW functions because I was using a GROUP BY clause and would need multiple windows.

I ended up using this hack:

WITH
  vars AS (
    SELECT '=D=' AS delimiter
      , 0 AS column_created_at
      , 1 AS column_lines
      , 2 AS column_other_field
  ),
  grouping_query AS (
    SELECT
      type_id, book_id, title_id, page_id,
      created_at -- 0, order column
        || delimiter || lines -- 1
        || delimiter || other_field -- 2
        AS info_by_created_at
    FROM
      tableX, vars
  )
SELECT
  type_id, book_id, title_id, page_id,
  split_part(MIN(info_by_created_at), delimiter, column_lines) AS min_lines_by_created_at
FROM
  grouping_query, vars
GROUP BY
  type_id, book_id, title_id, page_id