Re: How ad an increasing index to a query result?

On Sun, Oct 18, 2009 at 12:00 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> On 18/10/2009 11:30, Alban Hertroys wrote:
>
>> Short of enumerating those results in your application, the easiest
>> approach is probably to wrap your query in a join with generate_series
>> like so:
>>
>> SELECT a, s.b
>> FROM (
>>     SELECT a
>>     FROM table1
>>     ORDER BY a DESC LIMIT 5
>> ) AS t1, generate_series(5, 1, -1) AS s(b)
>>
>
> Won't that just give you the cartesian product of the two sets? I tried
> something similar yesterday out of curiosity, and that's what I got.
>
> The only things I can think of are (i) as you say, enumerate the results
> in the application or (ii) use a temporary sequence as someone else
> suggested.

yeah, the above gives a cartesian product.  Row number is really the
way to go here.  Using pre window tactics, it looks like we need:

select a, b from
(
  select a, nextval('c')  as b from
  (
    SELECT a,
    FROM table1
    ORDER BY a DESC LIMIT 5
  ) q order by a
) q order by a desc;

aside: it's never a good idea to write queries like this:
select func(), foo from bar order by foo limit x;
if you are concerned about how many times foo executes.  This is a
huge gotcha that constantly comes up on the lists.  see the warning
here:

http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html#ADVISORY-LOCKS

merlin