agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Stanton Schmidt <sschmidt@rgllogistics.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Jeff Fletcher <jeff.fletcher@gmail.com>
Cc: Martin Stöcker <martin.stoecker@stb-datenservice.de>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Query question
Date: Thu, 8 Mar 2018 14:17:58 -0600 (CST)
Message-ID: <182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com> (raw)
In-Reply-To: <CAKFQuwb2QckhL-OMhiXa2uG4cd+fj6k_5X8YLPnqnNv+9DYXLQ@mail.gmail.com>
References: <415803152.48510563.1520535505737.JavaMail.zimbra@rglholdings.com>
<e22d829f-77f2-86c9-e89d-6885bc1fc56d@stb-datenservice.de>
<557870630.48523473.1520536627693.JavaMail.zimbra@rglholdings.com>
<CABsD9ZNNiQxo=PQEW9xnXsWysGg6vntxC=TYxoh20E6DcG74yA@mail.gmail.com>
<CAKFQuwb2QckhL-OMhiXa2uG4cd+fj6k_5X8YLPnqnNv+9DYXLQ@mail.gmail.com>
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
direct. [ callto:920.884.1281 | 920. ] 471.4495 cell 920.660.1828
RGL
GO AHEAD. ASK WHAT IF.
[ http://www.rgllogistics.com/ | 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
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.
On Thu, Mar 8, 2018 at 12:20 PM, Jeff Fletcher < [ mailto:jeff.fletcher@gmail.com | jeff.fletcher@gmail.com ] > wrote:
Partition by is your friend...
[ https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_questions_1124603_grouped-2Dl... | https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-ea... [stackoverflow.com] ]
On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt < [ mailto:sschmidt@rgllogistics.com | sschmidt@rgllogistics.com ] > wrote:
BQ_BEGIN
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_desc
from (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) b
where a.equipment_id = b.equipment_id
but all I end up with is 5 total records.
Stanton
From: "Martin Stöcker" < [ mailto:martin.stoecker@stb-datenservice.de | martin.stoecker@stb-datenservice.de ] >
To: "pgsql-sql" < [ mailto:pgsql-sql@lists.postgresql.org | pgsql-sql@lists.postgresql.org ] >
Sent: Thursday, March 8, 2018 1:07:30 PM
Subject: Re: Query question
My first idea is to select all equipments and lateral join them to the 5 most recent events
Regards Martin
Am 08.03.2018 um 19:58 schrieb Stanton Schmidt:
BQ_BEGIN
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 schmidt
Database Administrator
direct. [ callto:920.884.1281 | 920. ] 471.4495 cell [ tel:(920)%20660-1828 | 920.660.1828 ]
RGL
GO AHEAD. ASK WHAT IF.
[ http://www.rgllogistics.com/ | www.RGLlogistics.co
m ]
BQ_END
BQ_END
view thread (15+ messages)
Message-ID: <182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com>
Permalink: ../182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com/
Also on: postgresql.org/message-id/182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sschmidt@rgllogistics.com, david.g.johnston@gmail.com, jeff.fletcher@gmail.com, martin.stoecker@stb-datenservice.de, pgsql-sql@lists.postgresql.org
Subject: Re: Query question
In-Reply-To: <182416290.48557370.1520540278348.JavaMail.zimbra@rglholdings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox