How to mount a SELECT with nonexistent numeric column ordered

Question

How to mount a SELECT with nonexistent numeric column ordered

Navigation

#1 by (5 votes)
#2 by (2 votes)

3

I want to do a SELECT with some columns already in the database and add a ordered numeric column starting at 1 for each row (I do not want the ID). I'm using PostgreSQL 8.4.

Example:

SELECT 
    descricao_produto, 
    preco,
    colunaDinamicaOrdenada AS ordem
FROM produtos;

Where colunaDinamicaOrdenada would be a command, subselect, or something of the type that forms a sequence of ordered numbers starting at 1.

Desired result:

descricao_produto | preco | ordem
Maçã                5,90    1
Banana              4,00    2
Melancia            7,00    3
Laranja             3,00    4

postgresql select

asked by anonymous 28.01.2016 / 21:17

2 answers

2

Speak Edson! I do not understand if you want an accountant or a "ranking". For a counter just set a variable and add it in select:

SET @colunaDinamicaOrdenada:=0;
SELECT descricao_produto, preco,
@colunaDinamicaOrdenada:=@colunaDinamicaOrdenada+1 AS ordem
FROM produtos;

Now if you want to do a ranking. There you will have to make a count and group the columns (or the one you want to rank). Example:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, descricao_produto, preço, COUNT(*) as       ordercount
 FROM produtos
 GROUP BY  descricao_produto, preço
 ORDER BY ordercount DESC;

28.01.2016 / 22:06

Is it possible to store an ArrayList in a Java Database? serialize class item in textual documents

score 5 · Accepted Answer

5

Use the function row_number ()

SELECT 
    descricao_produto, 
    preco,
    row_number() OVER (ORDER BY descricao_produto) AS ordem
FROM produtos;

28.01.2016 / 22:04