0

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
5
  • Where do the 11 and 2015-03-02 in the result come from? 12 is bigger than 11. – ypercubeᵀᴹ Jul 15 '15 at 9:33
  • How should ties be handled? – Lennart Jul 15 '15 at 9:36
  • @Lennart If its a tie, min and max will have the same value. – RockNinja Jul 15 '15 at 9:37
  • @alex the question is, if there are ties, the created_at column may have different values. Which should be shown then? – ypercubeᵀᴹ Jul 15 '15 at 9:45
  • @ypercube where created_at has the smallest value. – RockNinja Jul 15 '15 at 9:51
5

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.

2
  • The WINDOW construction is neat, haven't seen that one before, thanks – Lennart Jul 15 '15 at 10:29
  • In that case, you may want only one window and use both first_value and last_value – Ulysse BN Dec 19 '19 at 22:58
0

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

0

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

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.