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 <jeff(dot)fletcher(at)gmail(dot)com>
wrote:

> Partition by is your friend...
>
> https://stackoverflow.com/questions/1124603/grouped-
> limit-in-postgresql-show-the-first-n-rows-for-each-group
>
>
> On Thu, Mar 8, 2018 at 1:17 PM, Stanton Schmidt <sschmidt(at)rgllogistics(dot)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_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" <martin(dot)stoecker(at)stb-datenservice(dot)de>
>> *To: *"pgsql-sql" <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:
>>
>> 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. 920. <callto:920.884.1281>471.4495 cell 920.660.1828
>> <(920)%20660-1828>
>>
>> RGL
>> GO AHEAD. ASK WHAT IF.
>> www.RGLlogistics.co m <http://www.rgllogistics.com/>
>>
>>
>>
>>
>