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

Select ordered rows up to certain id – Postgresql

INSERT INTO "public"."catalog_element" 
("id", "catalogue_id", "element_id", "weight") VALUES
(1,100,1,0),
(2,100,2,1),
(3,100,3,2),
(4,10,1,0),
(5,10,5,0),
(6,10,6,1),
(7,100,7,1);

Let say, we have a Postgres table of catalog *- to -* element matching rows that are weighted, like such

id catalogue_id element_id weight
1 100 1 0
2 100 2 1
3 100 3 2
4 10 1 0
5 10 5 0
6 10 6 1
7 100 7 1

I try to write a query that will return records bounded to certain catalog_id, ordered by weight, in a range between first to one that have certain element_id.

Like this where catalog_id = 100 ordered DESC by weight and every rows up until element_id = 7 meet , which end up with this result:

id catalogue_id element_id weight
3 100 3 2
2 100 2 1
7 100 7 1
Tags:

5

Answers


  1. Chosen as BEST ANSWER
    0

    I figure out the way to do what I desire, but it's to clunky

    WITH ordered_catalogue_elements AS (
    SELECT *, ROW_NUMBER () OVER (ORDER BY weight DESC, id ASC)
    FROM catalogue
    WHERE 
        catalogue_id = 100
)
SELECT *
FROM ordered_catalogue_elements
WHERE row_number <= (
    SELECT row_number
    FROM ordered_catalogue_elements
    WHERE element_id = 7
);

  2. 0

    To select ordered rows up to a certain ID on the data in the given insert statement, you can use the following SQL query:

        SELECT * FROM catalog_element
    WHERE id <= <max_id>
    ORDER BY catalogue_id ASC, weight ASC, element_id ASC;

    Replace <max_id> with the maximum ID value that you want to include in the results. This query will select all rows from the catalog_element table where the ID value is less than or equal to the specified maximum ID, and order the results first by catalogue_id in ascending order, then by weight in ascending order, and finally by element_id in ascending order.

    For example, if you want to select all rows with an ID value less than or equal to 5, the query would be:

    SELECT * FROM catalog_element
WHERE id <= 5
ORDER BY catalogue_id ASC, weight ASC, element_id ASC;

    This would return the following rows:

    id  | catalogue_id | element_id | weight
----+-------------+------------+-------
1   | 100         | 1          | 0
2   | 100         | 2          | 1
3   | 100         | 3          | 2
4   | 10          | 1          | 0
5   | 10          | 5          | 0
    Login or Signup to reply.
  3. 0

    You can use a subquery to get the weight wanted and the select the column that are bigger than that weight

    SELECT
"id", "catalogue_id", "element_id", "weight"
FROM catalog_element
WHERE "catalogue_id" = 100 AND weight >= (SELECT weight FROM catalog_element WHERE "element_id" = 7)
    id catalogue_id element_id weight
    2 100 2 1
    3 100 3 2
    7 100 7 1 
    SELECT 3

    fiddle

    Login or Signup to reply.
  4. 0

    I’m not sure if you consider my solution less clunky.
    It does avoid the self join, which should result in better performance for large tables.

    select * from (
  select
    coalesce(lag(max_flag,1) over (order by weight desc, flag),0) lmf,
    wmf.*
  from (
    select 
      max(flag) over( order by weight desc, flag) max_flag,
      wf.* 
    from (
      select
        case
          when element_id = 7 then 1
          else 0
        end flag,
        ce.* 
      from catalog_element ce
      where catalogue_id = 100
    ) wf
  ) wmf
) wlmf
where lmf =0;

    yields

    lmf max_flag flag id catalogue_id element_id weight
    0 0 0 3 100 3 2
    0 0 0 2 100 2 1
    0 1 1 7 100 7 1

    With the following setup

    create table catalog_element (
 id integer, 
 catalogue_id integer, 
 element_id integer, 
 weight integer
);


INSERT INTO catalog_element 
(id, catalogue_id, element_id, weight) VALUES
(1,100,1,0),
(2,100,2,1),
(3,100,3,2),
(4,10,1,0),
(5,10,5,0),
(6,10,6,1),
(7,100,7,1);

    While statements like this seem intimidating they are actually not that hard to understand.

    1. You used analytic or windowing functions yourself, so I assume basic knowledge of their working.

    2. since you can’t directly nest such functions I use nested selects, where each nesting level adds a function. Therefore such statements should be read from the inside out.

    3. The inner most one wf for with_flag adds a flag column using

       case
     when element_id = 7 then 1
     else 0
 end flag

      marking the cut off row. This happens in its own subselect, since
      we need the flag twice in the next step.

    4. In wmf for with_max_flag we use max(flag) over( order by weight desc, flag) to create the max_flag column that has a 0 for all rows before the cut off row. Not that we order by flag as well to have 0 also for those rows with the same weight as the cut off row.

    5. We can now almost filter by that row, but we want to include the row cut off row, so we us lag to basically shift the max_flag by one row, and use coalesce to turn null values into 0.
      This happens in wlmf for with_lagged_max_flag.

    6. We finally filter out only the rows that we want.

    The resulting SQL statement looks clumsy, but it does access the data only once and orders by the same criteria all the time.
    My experience with similar and actually way bigger statements is that they perform extremely well.

    View on DB Fiddle

    Login or Signup to reply.
  5. 0

    Using LATERAL to compute max relevant weight. Take all the rows with greater weight and only rows till element_id = 7 having exactly that weight. May return more than one row for element_id = 7. Define an extra criteria to select a single element_id = 7 row.

    select "id", "catalogue_id", "element_id", "weight"
from catalog_element e
cross join lateral (
    SELECT max(e2.weight) maxw
    FROM catalog_element e2 
    WHERE e2."element_id" = 7 and e2.catalogue_id = e.catalogue_id 
) t 
where "catalogue_id" = 100 
   and weight >= t.maxw   
   and (weight > t.maxw or element_id <= 7)
order by weight desc, element_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search