Postgresql, Select from Max Id

PostgreSQL, SELECT from max id

If your goal is to get the row with the highest my_id value, then the following query should achieve the same goal.

SELECT my_id, col2, col3 
FROM mytable 
ORDER BY my_id DESC 
LIMIT 1

Postgres select max id only if value of another column is negative

SELECT T.*
FROM
(
  SELECT *, RANK() OVER(PARTITION BY user_id ORDER BY id DESC) RNK 
  FROM trans_tbl
 ) T
 WHERE T.RNK = 1 AND T.ending_balance < 0;

Fiddle

Adding max id from a different table to a query in PostgreSQL

Could it be a solution?

=# SELECT name AS name,
          (SELECT MAX(id) FROM your_table) AS max_id
   FROM really_big_table;

       name       │ max_id
──────────────────┼─────────
 44e5f8fc1f75fad2 │ 5307021
 97fc0aa3a5e3da98 │ 5307021
 ccb83afb938ad758 │ 5307021
 523bf25914e5c9c0 │ 5307021
 d2362badeb2eb7ea │ 5307021
 b6e0b87df6d41523 │ 5307021
 da754db1fa226453 │ 5307021
 865d0e177cb0a3e8 │ 5307021
 5904a07a273d8cba │ 5307021
 952c75ef37d47dab │ 5307021
(10 rows)

select max value for each ID (including the possibility of multiple max values for one ID!)

One way would be to use a CTE:

WITH themaxes AS
(SELECT id, max(score) AS maxscore FROM tbl GROUP BY ID)
SELECT t.* FROM tbl t INNER JOIN themaxes m ON m.id = t.id AND m.maxscore = t.score;

another way would be to use a window function (this example uses a subquery with an alias):

SELECT id,score FROM 
(SELECT rank() OVER (PARTITION BY id ORDER BY score DESC) AS therank, * FROM tbl) t 
WHERE therank = 1

Postgresql select max query takes long time

With this index

create index on app_weatherdata(sensor_id, local_id, id desc);

this should be quite fast:

select *
from app_weatherdata 
where local_id = 3
and sensor_id = 1
order by id desc
limit 1

On a test table with 25 million rows and 100 distinct sensors and 100 distinct locations, I get this execution plan:

Limit  (cost=0.56..4.60 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using app_weatherdata_sensor_id_local_id_id_idx on app_weatherdata  (cost=0.56..4627.36 rows=1147 width=32) (actual time=0.020..0.020 rows=1 loops=1)
        Index Cond: ((sensor_id = 1) AND (local_id = 3))
        Buffers: shared hit=5
Planning Time: 0.085 ms
Execution Time: 0.032 ms

How to select id with max date group by category in PostgreSQL?

This is a perfect use-case for DISTINCT ON - a Postgres specific extension of the standard DISTINCT:

SELECT DISTINCT ON (category)
       id  -- , category, date  -- any other column (expression) from the same row
FROM   tbl
ORDER  BY category, date DESC;

Careful with descending sort order. If the column can be NULL, you may want to add NULLS LAST:

• Sort by column ASC, but NULL values first?

DISTINCT ON is simple and fast. Detailed explanation in this related answer:

• Select first row in each GROUP BY group?

For big tables with many rows per category consider an alternative approach:

• Optimize GROUP BY query to retrieve latest row per user
• Optimize groupwise maximum query

SQL: Select Distinct and Max shows the lowest value instead of the highest

Your use of DISTINCT ON is incorrect, and you don't need to be using GROUP BY here. Just list in parentheses in the select clause the column used for considering each group. Then repeat that column first in the ORDER BY clause, followed by the column to be used for choosing the first record in each group.

SELECT DISTINCT ON (hbg.group_id)
    hbg.group_id,
    hbg.id AS id,
    hbg.group_name,
    hbg.group_description,
    hbg.group_type_id,
    hbgt.group_type_name,
    hbg.category_id,
    hbg.effective_start_datetime,
    hbg.created_by,
    hbg.created_datetime,
    hbg.archived_by,
    hbg.archived_datetime
FROM hms_bbr_group hbg
LEFT JOIN hms_bbr_group_type hbgt
    ON hbg.group_type_id = hbgt.group_type_id
ORDER BY
    hbg.group_id,
    hbg.id DESC;

Pass SELECT MAX(`Id`) FROM Table to setval()

I found working workaround using prepared statement:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
EXECUTE IMMEDIATE CONCAT('SELECT SETVAL(`MySequence`, ', @max_value, ')');

---

Related Topics