Re: Query question
"David G. Johnston" <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAKFQuwb2QckhL-OMhiXa2uG4cd+fj6k_5X8YLPnqnNv+9DYXLQ@mail.gmail.com> |
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 <[email protected]> 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 <[email protected] > > 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" <[email protected]> >> *To: *"pgsql-sql" <[email protected]> >> *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/> >> >> >> >> >