# Capture row with highest value from each percentile

So far I've tried doing this:

``````SELECT MAX(buckets.position_id), NTILE
FROM  (
SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
FROM position
WHERE activity_id = 8
) AS buckets
GROUP BY  NTILE ORDER BY 2;
``````

Which outputs something like this:

`````` max | ntile
-----+-------
3 |     1
6 |     2
9 |     3
12 |     4
15 |     5
18 |     6
21 |     7
24 |     8
27 |     9
30 |    10
33 |    11
36 |    12
``````

But this just shows the id along with the percentile of that id in the row, I need to show the complete row every time, so I tried doing this:

``````SELECT MAX(buckets.position_id), NTILE
FROM  (
SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
FROM position
WHERE activity_id = 8
) AS buckets
FULL JOIN position ON position.position_id = buckets.position_id
GROUP BY  NTILE ORDER BY 2;
``````

Which shows the same output.

``````SELECT MAX(buckets.position_id), buckets, buckets.ntile
FROM  (
SELECT position.position_id, position.lat, position.long, position.activity_id, position.position_created_at , NTILE(100) OVER (ORDER BY position.position_id)
FROM position
WHERE activity_id = 8
) AS buckets
GROUP BY buckets, buckets.ntile
ORDER BY 3;
``````

This outputs something like this, which is what I want, but it's repeating the NTILE instead of just getting the last one.

`````` max |                                       buckets                                       | ntile
-----+-------------------------------------------------------------------------------------+-------
1 | (1,20,90,12,12,8,"2021-03-12 21:07:47.8646",1)                                      |     1
2 | (2,20,90,2,12,8,"2021-03-12 21:08:57.59163",1)                                      |     1
3 | (3,20,90,2,12,8,"2021-03-12 21:09:21.355993",1)                                     |     1
4 | (4,20,90,2,12,8,"2021-03-12 21:09:36.752904",2)                                     |     2
5 | (5,20,90,2,12,8,"2021-03-12 21:09:45.815941",2)                                     |     2
6 | (6,20,90,2.001,12.001,8,"2021-03-15 12:11:36.320269",2)                             |     2
7 | (7,20,90,2.001,12.001,8,"2021-03-15 12:11:38.876406",3)                             |     3
8 | (8,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:41.911301",3)                 |     3
9 | (9,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:44.938797",3)                 |     3
10 | (10,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:47.975407",4)   |     4
11 | (11,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:51.003433",4)   |     4
12 | (12,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:54.035314",4)   |     4
13 | (13,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:57.067232",5)   |     5
14 | (14,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:00.099153",5)   |     5
15 | (15,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:03.131785",5)   |     5
16 | (16,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:06.171215",6)   |     6
17 | (17,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:09.200098",6)   |     6
``````

But since those values change every row, the Max function messes up and it just shows every row instead of the last percentile...

I also tried wrapping everything inside `MAX()` functions, but I'm afraid that when I get a lower value from either `lat` or `long` it's going to mess up. (Also I don't think that's very efficient, correct me if I'm wrong.)

DB-fiddle for testing https://www.db-fiddle.com/f/gKoqqmRa47g5546EMz5Hs/0

• can you show us the wanted result for your fiddle?
– nbk
Commented Mar 17, 2021 at 17:36
• You need to aggregate the `buckets` field to turn it from a SETOF to a single row. `(array_agg(\$X ORDER BY \$Y))[1]` should do what you want; use the ORDER BY to control which of the rows in that ntile group is used. db-fiddle.com/f/myA71qS3du4EyTjJwo1e8p/0 Commented Mar 17, 2021 at 18:08
• @AdamKG It's still showing all of the rows, I just want the last of every Ntile iteration. I.E. if there are 20 rows, I don't want it to show every Ntile Like 1,1,2,2,3,3,4,4........ Instead I need it to just show me the last one of each iteration, like 1,2,3,4. Commented Mar 17, 2021 at 19:21
• @AdamKG I didn't know that array_agg function existed, it saved my life, thanks! you just remove buckets from the order by clause and it works just as I need it to! Commented Mar 17, 2021 at 19:25

If I understand correctly your objective is this:

After ordering by `position_id`, get the row with the highest `position_id` for each of 100 percentiles.

`DISTINCT ON` does it:

``````SELECT DISTINCT ON (ntile)
NTILE(100) OVER (ORDER BY position_id) AS ntile, *
FROM   position
WHERE  activity_id = 1
ORDER  BY ntile, position_id DESC;
``````

db<>fiddle here

Since the PK is on `(position_id, activity_id)`, `position_id` is unique for each `activity_id` and we need no tiebreaker.

Can be a single `SELECT` without subquery because `DISTINCT ON` is applied after window functions.

See:

• YES! this was it, thank you! Commented Mar 18, 2021 at 13:01