[SQL] Query with LIMIT clause
I have the following records that I get from a query, the fields are date type in day/month/year format: - Initial Final DateDate - 27/08/2012 04/09/2012 29/08/2012 09/09/2012 28/08/2012 09/09/2012 30/08/2012 09/09/2012 30/08/2012 09/09/2012 27/08/2012 09/09/2012 31/08/2012 09/09/2012 28/08/2012 10/09/2012 05/09/2012 16/09/2012 As you can see, this result is ordered by Final Date. What I need is to get the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I have tried an ORDEY BY DESC but the result is the same. I will very much appreciate your comments. Respectfully, Jorge Maldonado
Re: [SQL] Query with LIMIT clause
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of JORGE MALDONADO Sent: Sunday, September 09, 2012 1:26 PM To: pgsql-sql@postgresql.org Subject: [SQL] Query with LIMIT clause I have the following records that I get from a query, the fields are date type in day/month/year format: - Initial Final Date Date - 27/08/2012 04/09/2012 29/08/2012 09/09/2012 28/08/2012 09/09/2012 30/08/2012 09/09/2012 30/08/2012 09/09/2012 27/08/2012 09/09/2012 31/08/2012 09/09/2012 28/08/2012 10/09/2012 05/09/2012 16/09/2012 As you can see, this result is ordered by Final Date. What I need is to get the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I have tried an ORDEY BY DESC but the result is the same. I will very much appreciate your comments. . First, you should really order by both columns, not just "final date". The ties on 9/9/12 are unordered. Second, you will indeed need to reverse the sort order and then take the first 5 records; figuring out and limiting on the last 5 isn't worth the effort. SELECT initial_date, final_date FROM date_source ORDER BY final_date DESC, initial_date DESC LIMIT 5 You can put the above into a sub-query and re-order if the final result is needed in ascending order. If this doesn't seem to work you will want to provide the exact query/queries you are trying so that someone may spot what you are doing wrong. Dave -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] returning values from dynamic SQL to a variable
I have a PG function ( using plpgsql) that calls a number of sub functions also in plpgsql. I have two main problems that seem to be variations on the same theme of running dynamic SQL from a variable with the EXECUTE statement and returning the results back to a variable defined in the calling function. Problem 1: return the results of a table query to a variable. I have a logging table that my sub functions write to. At the beginning of my main function I want to read a run number from the logging table and increment it by one to then pass into my sub functions. I've properly declared the variable (v_runnumber) and the data type is correct. The following statement works fine in the main function and stores the value in the variable. select max(runnumber) into v_runnumber from MySchema.log_table; However, MySchema is a parameter that gets passed into the main function because I need this to work for multiple schemas. If I try and make this dynamic by using the following statement: Sql := 'select max(run number) into v_runnumber from ' || MySchema || '.log_table;'; Execute Sql; I get the following error message (even though the resulting value in the text variable Sql is valid code): ERROR: query string argument of EXECUTE is null SQL state: 22004 Problem 2: returning the results of a function call to a variable. This is a similar issue to #1 but in this case, I'm calling a function from the main function and trying to get the return value back (a single integer) from the sub function to test for errors. Again, I'm calling the function with dynamical SQL because of the need to take user values from the main function to call the sub functions. The function call: sql := 'select * from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');'; execute sql; Works fine. However when I try and store the value coming back from the function into a main variable with the following call I get an error: sql := 'select * into v_retcode from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');'; execute sql; "EXECUTE of SELECT ... INTO is not implemented"
Re: [SQL] Query with LIMIT clause
Firstly, who should I reply to, you or the list? Your solution is working pretty fine, I appreciate your advice. Now, I am sure that an index is a good idea in order to make the query fast. What would be a good criteria to define an index? Will an index for final date and another for initial date is the choice, or one index composed of both initial and final date? Regards, Jorge Maldonado On Sun, Sep 9, 2012 at 12:45 PM, David Johnston wrote: > From: pgsql-sql-ow...@postgresql.org [mailto: > pgsql-sql-ow...@postgresql.org] > On Behalf Of JORGE MALDONADO > Sent: Sunday, September 09, 2012 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Query with LIMIT clause > > I have the following records that I get from a query, the fields are date > type in day/month/year format: > > - > Initial Final > DateDate > - > 27/08/2012 04/09/2012 > 29/08/2012 09/09/2012 > 28/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 27/08/2012 09/09/2012 > 31/08/2012 09/09/2012 > 28/08/2012 10/09/2012 > 05/09/2012 16/09/2012 > > As you can see, this result is ordered by Final Date. What I need is to get > the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I > have tried an ORDEY BY DESC but the result is the same. I will very much > appreciate your comments. > > . > > First, you should really order by both columns, not just "final date". The > ties on 9/9/12 are unordered. > > Second, you will indeed need to reverse the sort order and then take the > first 5 records; figuring out and limiting on the last 5 isn't worth the > effort. > > SELECT initial_date, final_date > FROM date_source > ORDER BY final_date DESC, initial_date DESC > LIMIT 5 > > You can put the above into a sub-query and re-order if the final result is > needed in ascending order. > > If this doesn't seem to work you will want to provide the exact > query/queries you are trying so that someone may spot what you are doing > wrong. > > Dave > > >
Re: [SQL] Query with LIMIT clause
Always reply to list. It is also preferred to bottom-post. Indexes are never simple answers and there isn't enough info to really give good advice here. You should try different versions and estimate performance (read and write). My guess is that a compound index (2 columns) would work well though if you are going to filter/sort on initial date frequently (by itself) then it likely wants its own index anyway and having two separate indexes would be better. David J. On Sep 9, 2012, at 20:56, JORGE MALDONADO wrote: > Firstly, who should I reply to, you or the list? > Your solution is working pretty fine, I appreciate your advice. Now, I am > sure that an index is a good idea in order to make the query fast. What would > be a good criteria to define an index? Will an index for final date and > another for initial date is the choice, or one index composed of both initial > and final date? > > Regards, > Jorge Maldonado > > On Sun, Sep 9, 2012 at 12:45 PM, David Johnston wrote: > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > On Behalf Of JORGE MALDONADO > Sent: Sunday, September 09, 2012 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Query with LIMIT clause > > I have the following records that I get from a query, the fields are date > type in day/month/year format: > > - > Initial Final > DateDate > - > 27/08/2012 04/09/2012 > 29/08/2012 09/09/2012 > 28/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 27/08/2012 09/09/2012 > 31/08/2012 09/09/2012 > 28/08/2012 10/09/2012 > 05/09/2012 16/09/2012 > > As you can see, this result is ordered by Final Date. What I need is to get > the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I > have tried an ORDEY BY DESC but the result is the same. I will very much > appreciate your comments. > > . > > First, you should really order by both columns, not just "final date". The > ties on 9/9/12 are unordered. > > Second, you will indeed need to reverse the sort order and then take the > first 5 records; figuring out and limiting on the last 5 isn't worth the > effort. > > SELECT initial_date, final_date > FROM date_source > ORDER BY final_date DESC, initial_date DESC > LIMIT 5 > > You can put the above into a sub-query and re-order if the final result is > needed in ascending order. > > If this doesn't seem to work you will want to provide the exact > query/queries you are trying so that someone may spot what you are doing > wrong. > > Dave > > >
