Re: Query question
| От | Stanton Schmidt |
|---|---|
| Тема | Re: Query question |
| Дата | |
| Msg-id | 182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com обсуждение исходный текст |
| Ответ на | Re: Query question ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-sql |
I should have mentioned which version I am using. We are at version 9.2 and the Lateral was not introduced until version 9.3.
The partitioning worked Thanks.
stanton schmidt
Database Administrator
RGL
direct. 920.471.4495 cell 920.660.1828
RGL
GO AHEAD. ASK WHAT IF.
www.RGLlogistics.co m
www.RGLlogistics.co m
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: "Jeff Fletcher" <jeff.fletcher@gmail.com>
Cc: "Stanton Schmidt" <sschmidt@rgllogistics.com>, "Martin Stöcker" <martin.stoecker@stb-datenservice.de>, "pgsql-sql" <pgsql-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 1:28:31 PM
Subject: Re: Query question
To: "Jeff Fletcher" <jeff.fletcher@gmail.com>
Cc: "Stanton Schmidt" <sschmidt@rgllogistics.com>, "Martin Stöcker" <martin.stoecker@stb-datenservice.de>, "pgsql-sql" <pgsql-sql@lists.postgresql.org>
Sent: Thursday, March 8, 2018 1:28:31 PM
Subject: Re: Query question
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