How to mount a SELECT with ordered nonexistent numeric column

Asked

Viewed 1,401 times

3

I want to make a SELECT with some existing columns in the database and add a ordered numerical column starting at 1 for each line (I don’t 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 kind 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

2 answers

5


Use the function row_number()

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

    Guy this row_number() was what I needed to do this and ended up helping me A LOT in other commands that I needed to do then, thanks! I didn’t even know about those window functions

2

Edson speaks! I did not understand well if you want an accountant or a "ranking". For an accountant just set a variable and add in select:

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

Now if you want to make a ranking. Ai 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;

Browser other questions tagged postgresql select

You are not signed in. Login or sign up in order to post.