[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

PostgresSQL get last 20 rows and then get max value

Hi

I have a table that I need to list the last 20 rows from end_time and then base off that get the max value of the column run_time.



Example:

select run_time_sec,end_time FROM runinfo_history
where sched_table = 'TEST_TIMES'
  and job_mem_name = 'TEST_TIMES'
  and data_center = 'DEV'
order BY end_time DESC LIMIT 20

gets the last 20

run_time_sec     end_time            
 ---------------  --------------------
 4                8/19/2014 6:25:39 PM
 4                8/19/2014 6:25:07 PM
 4                8/19/2014 6:24:29 PM
 3                8/19/2014 6:23:40 PM
 3                8/19/2014 6:23:11 PM
 3                8/19/2014 6:23:01 PM
 4                8/19/2014 6:22:51 PM
 3                8/19/2014 6:22:41 PM
 4                8/19/2014 6:22:32 PM
 4                8/19/2014 6:22:21 PM
 4                8/19/2014 6:21:32 PM
 4                8/19/2014 6:21:22 PM
 4                8/19/2014 6:21:12 PM
 4                8/19/2014 6:20:52 PM
 4                8/19/2014 6:20:42 PM
 4                8/19/2014 6:20:32 PM
 3                8/19/2014 6:20:22 PM
 3                8/19/2014 6:20:02 PM
 3                8/19/2014 6:19:50 PM
 60               8/19/2014 6:11:13 PM


I do not need the column end_time. Its just hear as to show what it is but used to get the last 20 dates from that column.

Not sure who to combine to get max from the list.

Thanks

Mike
0
mikeysmailbox1
Asked:
mikeysmailbox1
1 Solution
 
Phil PhillipsDirector of DevOpsCommented:
You can get the max run_time_sec from your query with something like:

select max(run_time_sec) from (
  select run_time_sec FROM runinfo_history
    where sched_table = 'TEST_TIMES'
      and job_mem_name = 'TEST_TIMES'
      and data_center = 'DEV'
      order BY end_time DESC LIMIT 20
) as t1;

Open in new window

0
 
mikeysmailbox1Author Commented:
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now