Re: Query question
От | David G. Johnston |
---|---|
Тема | Re: Query question |
Дата | |
Msg-id | CAKFQuwb2QckhL-OMhiXa2uG4cd+fj6k_5X8YLPnqnNv+9DYXLQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Query question (Jeff Fletcher <jeff.fletcher@gmail.com>) |
Ответы |
Re: Query question
(Stanton Schmidt <sschmidt@rgllogistics.com>)
|
Список | pgsql-sql |
We prefer to avoid top-posting on these lists but I'll go with the flow here...
<not tested, syntax is close but might have errors>
select *
from (select distinct equipment_id from log_table) a,
lateral get_log_data_for_equipment(equipment_id, 5)
then write the get_log_data_for_equipment function as dynamic sql substituting the desired limit value.
Not saying the above is going to be well performing but in terms of simplicity of the top-level query it scores high.
If you have an "equipment" table, which you should, replacing the select distinct with select should be considered. The presence of "distinct" (without 'on') is a code smell for me.
David J.
Partition by is your friend...On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt <sschmidt@rgllogistics.com> wrote:So far I have been unable to figure out how to do that.I tried:select a.equipment_id, b.log_date, b.log_time, b.event_descfrom (select distinct equipment_id from log_table) a, (select equipment_id, log_date, log_time, event_desc from log_table order by log_date desc, log_time desc limit 5) bwhere a.equipment_id = b.equipment_idbut all I end up with is 5 total records.StantonFrom: "Martin Stöcker" <martin.stoecker@stb-datenservice.de>
To: "pgsql-sql" <pgsql-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 1:07:30 PM
Subject: Re: Query questionMy first idea is to select all equipments and lateral join them to the 5 most recent events
Regards MartinAm 08.03.2018 um 19:58 schrieb Stanton Schmidt:Hi,I am new to the list so feel free to let me know if I am out of line.My question is:I have a table that has log events for pieces of equipment. For each piece of equipment this table may contain 1 or more (hundreds potentially).I need to write a query that will return only the last 5 log events for each (and every) piece of equipment.log_table (equipment_id character(30),log_date date,log_time time,event_desc text)Thanks for your help.stanton schmidtDatabase Administratordirect. 920.471.4495 cell 920.660.1828
RGLGO AHEAD. ASK WHAT IF.
www.RGLlogistics.co m
В списке pgsql-sql по дате отправления: