Re: Query question

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(dot)g(dot)johnston(at)gmail(dot)com>
To: "Jeff Fletcher" <jeff(dot)fletcher(at)gmail(dot)com>
Cc: "Stanton Schmidt" <sschmidt(at)rgllogistics(dot)com>, "Martin Stöcker" <martin(dot)stoecker(at)stb-datenservice(dot)de>, "pgsql-sql" <pgsql-sql(at)lists(dot)postgresql(dot)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(dot)fletcher(at)gmail(dot)com | jeff(dot)fletcher(at)gmail(dot)com ] > wrote:

Partition by is your friend...

[ https://urldefense.proofpoint.com/v2/url?u=https-3A__stackoverflow.com_questions_1124603_grouped-2Dlimit-2Din-2Dpostgresql-2Dshow-2Dthe-2Dfirst-2Dn-2Drows-2Dfor-2Deach-2Dgroup&d=DwMFaQ&c=eLkx2stMcShI0L6xvxICXHnFB9zmDvZmvnhsCd8Gf8M&r=EWbtHQpXIg2XbQduIylzyXrAFdbjWaMyy-p_LkyfjhQ&m=DEdLcsFrDyuVKOvYidkUgnvb5VlWOA5TPoLM_4cEMbI&s=PGsyF58Wm2BowXszKbPpE1wx40C7KcKhewcEIXUILas&e= | https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group [stackoverflow.com] ]

On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt < [ mailto:sschmidt(at)rgllogistics(dot)com | sschmidt(at)rgllogistics(dot)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(dot)stoecker(at)stb-datenservice(dot)de | martin(dot)stoecker(at)stb-datenservice(dot)de ] >
To: "pgsql-sql" < [ mailto:pgsql-sql(at)lists(dot)postgresql(dot)org | pgsql-sql(at)lists(dot)postgresql(dot)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