Thread: How ad an increasing index to a query result?
Hello,
Could somebody please try to help me with this problem?
So, let’s say that I have the query:
CREATE SEQUENCE c START 1;
SELECT a, nextval('c') as b
FROM table1
ORDER BY a DESC LIMIT 5;
I.e., I want to pick the 5 largest entries from table1 and show them
alongside a new index column that tells the position of the entry. For
example:
a | b
--------
82 | 5
79 | 4
34 | 3
12 | 2
11 | 1
However, when I try this approach, the values of column b don’t follow
the correct order. How should I go about and modify my code?
I found an article that should help you with the answer: http://explainextended.com/2009/05/05/postgresql-row-numbers/ ROWNUM is a very useful pseudocolumn in Oracle that returns the position of each row in a final dataset. Upcoming PostgreSQL 8.4 will have this pseudocolumn, but as for now will we need a hack to access it. (in 8,3) The main idea is simple: 1. Wrap the query results into an array 2. Join this array with a generate_series() so that numbers from 1 to array_upper() are returned 3. For each row returned, return this number (as ROWNUM) along the corresponding array member (which is the row from the original query) ... See original article for the code Hope it helps Andy Bailey On Wed, Oct 14, 2009 at 12:05 PM, Josip <josip.2000@gmail.com> wrote: > Hello, > > Could somebody please try to help me with this problem? > So, let’s say that I have the query: > > CREATE SEQUENCE c START 1; > > SELECT a, nextval('c') as b > FROM table1 > ORDER BY a DESC LIMIT 5; > > I.e., I want to pick the 5 largest entries from table1 and show them > alongside a new index column that tells the position of the entry. For > example: > > a | b > -------- > 82 | 5 > 79 | 4 > 34 | 3 > 12 | 2 > 11 | 1 > > However, when I try this approach, the values of column b don’t follow > the correct order. How should I go about and modify my code? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Andrew Bailey (312) 866 9556 NOTA DE CONFIDENCIALIDAD Y DE NO DIVULGACIÓN: La información contenida en este E-mail y sus archivos adjuntos es confidencial y sólo puede ser utilizada por el individuo o la empresa a la cual está dirigido. Si no es el receptor autorizado, cualquier retención, difusión, distribución o copia de este mensaje queda prohibida y sancionada por la ley. Si por error recibe este mensaje, favor devolverlo y borrarlo inmediatamente.
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
> --------
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
[Spotts, Christopher]
Sounds like you you want 8.4 and windowing functions like row_number().
(http://www.postgresql.org/docs/8.4/static/functions-window.html)
If you have 8.4.
Josip wrote:
> Hello,
>
> Could somebody please try to help me with this problem?
> So, let’s say that I have the query:
>
> CREATE SEQUENCE c START 1;
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
> --------
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
> However, when I try this approach, the values of column b don’t follow
> the correct order. How should I go about and modify my code?
>
You can try using a sequence with a negative increment like below
CREATE SEQUENCE test_sequence
INCREMENT -1
START 100
MINVALUE 1
MAXVALUE 100;
- mrLami
On 14 Oct 2009, at 19:05, Josip wrote:
> Hello,
>
> Could somebody please try to help me with this problem?
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
> --------
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
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)
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:737,4adaee3411688629581426!
2009/10/14 Josip <josip.2000@gmail.com>:
> Hello,
>
> Could somebody please try to help me with this problem?
> So, let’s say that I have the query:
>
> CREATE SEQUENCE c START 1;
>
> SELECT a, nextval('c') as b
> FROM table1
> ORDER BY a DESC LIMIT 5;
>
> I.e., I want to pick the 5 largest entries from table1 and show them
> alongside a new index column that tells the position of the entry. For
> example:
>
> a | b
> --------
> 82 | 5
> 79 | 4
> 34 | 3
> 12 | 2
> 11 | 1
>
> However, when I try this approach, the values of column b don’t follow
> the correct order. How should I go about and modify my code?
The problem here is that the sequence will merely act as a counter,
and knows nothing about the data you're using it to count against. In
the example you gave, it appears you're inserting data into the table
in ascending order, but you want it output in descending order.
If you're using PostgreSQL 8.4, you can do the following:
SELECT a, rank() OVER (ORDER BY a DESC)
FROM table1
ORDER BY a DESC
This is called a window function. It is using a window aggregate
function to rank column a in descending order (as declared in the OVER
clause).
If you are using an earlier version of Postgres, you could always use
a temporary table:
CREATE TEMP TABLE temp_table1
(
orderseq SERIAL PRIMARY KEY,
a INTEGER NOT NULL
) ON COMMIT DROP;
INSERT INTO temp_table1 (a)
SELECT a
FROM table1
ORDER BY a DESC;
SELECT orderseq, a
FROM temp_table1;
There may be another way, but can't think of one of the top of my head.
Thom
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. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
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