2/2/2024 0 Comments Postgres lag functionRow_number() over (PARTITION BY objekt_id ORDER BY start_date) as row_id From your question, but fixed by moving the `ORDER BY` into the window function It is possible with window functions, but while I'm on my phone I'm struggling to work out a concise answer as PostGreSQL doesn't have IGNORE NULLS. The goal with query is to choose a specific date and to tell if the object is for rent or not and then also show who rent's it at and who was the last one and is there someone in line to rent Wished Output: id start_date end_date objekt_id person_id last_person next_person Output now: id start_date end_date objekt_id person_id last_person next_person ![]() If person_id is empty i also want to see last and next person. Last or Next Person_id always have to either null or from another person_id.Īt the moment row 2 will give me last_person_id = 18268 since row 1 had the same person_id. Lead(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as next_person Lag(person_id) over (PARTITION BY objekt_id, person_id IS NOT NULL AND objekt_id IS NOT NULL ORDER BY id) as last_person, Row_number() over (PARTITION BY objekt_id) as row_id With this Query i almost solved my Problem but there is still one thing i need help to change. With lag and lead i want two columns for last person_id and next person_id. I have this table fiddle CREATE TABLE rent(id integer,start_date date, end_date date,objekt_id integer,person_id integer) ![]() ![]() Can someone help me with the last step of my Query.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |