Question 89673 in PostgreSQL Question posted in
The official documentation can be found here.

Find minimum in row – Postgresql

I have one table, example:

|    ID    | numbers  |
| -------- | -------- |
|     1    |   200    |
|     2    |   210    |
|     3    |   320    |
|     4    |   340    |
|     5    |   360    |
|     6    |   480    |
|     7    |   490    |
|     8    |   500    |
|     9    |   610    |
|     10   |   630    |
|     11   |   700    |

Everywhere where the number change more than 100 ( ID 3,(minimum 200 on ID 1 and ID 3 320 more than 100 ) so restart the minimum search) need to find the minimum after the jump.

first minimum ID 1 after have one jump on ID 3 then restart to search the minimum and the current minimum is 320 on ID 3, after again restart the search have one jump on ID 6 and restart the minimum search the minimum is 480 on ID 6, then jump again on ID 9, so restart the min search and the current minimum is 630 on ID 9.

General minimum search not take the highest number just after, I cannot set fix number in the code ( like ID number ) because later change. I have the query what is find the more than 100 jump, and restart the minimum search.

select pos.min(numbers) as minimumtemp from example_table;

Expected output in this case is minimum = 610

Any solution is help thank you!

Tags:

2

Answers


  1. 0

    One option is to use a running sum that increased by 1 whenever the value of the numbers jumps by 100+:

    with t as
  (
  select *,
    case when numbers - lag(numbers, 1, numbers) over (order by id) >= 100 then 1 else 0 end as grp
  from table_name
  )
select id, numbers from t
order by sum(grp) over (order by id) desc, numbers
limit 1

    see demo

    Login or Signup to reply.
  2. 0

    step-by-step demo:db<>fiddle

    SELECT DISTINCT ON (group_id)                            -- 6
    *
FROM (
    SELECT
        *,
        SUM(change) OVER (ORDER BY id) as group_id       -- 5
    FROM (
        SELECT
            *,
            COALESCE(                                    -- 3
                numbers 
                    - lag(numbers) OVER (ORDER BY id)    -- 1
                    >= 100,                              -- 2 
                true
            )::int as change                             -- 4
        FROM mytable
    ) s
) s
ORDER BY group_id, numbers
    1. Move the previous numbers value to the current record with lag() window function
    2. Calculate difference between current and previous numbers value and check if is >= 100
    3. Because first record has no previous numbers value, the difference calculation would result in NULL value. To avoid this, use COALESCE to set all NULL value to true (which represents a change of calculation group like your 100 jump)
    4. Cast the boolean values into int (true == 1, false == 0)
    5. Now all numbers jumps >= 100 (and the very first record) have an 1 marker, which represent the first record of a group. Do a cumulate SUM using an ordered window function to create group ids (each 1 record increases the id, whereas each 0 record gets the same id as the previous record, which means, it is the same group, meaning there was no 100 jump)
    6. Use DISTINCT ON (group_id) to get only one record per ordered group. The groups must be ordered by numbers, so that the minimum number becomes the first record per group, which is taken by the DISTINCT ON.

    Notice: If the numbers values are strictly increasing, you could stop at step 3 and simply use WHERE change == true to get the jump records, because they are automatically the records per group with the minimum value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search