how to sort by line number in a query in postgres?

Asked

Viewed 1,448 times

5

To popular the grid of a views I’m using the database, initially I put the table ID to be the view’s Dice, but when some record is deleted from the database the Dice is not sequential.

view no html

select no postgres

html postgresql
  • 1

    use windows. example select id_group,id_user, name, rank() over (order by id) from <table>

  • 1

    How much a column has the attribute AUTO INCREMENT you don’t have to worry about it, since the value will be automatically placed for you when entering, and it will also always be sequential even if you delete a record.

1 answer

3


From what I understand you would like to have a sequence of 1 a n no matter the value of the field id.

I think it would be something like this:

SELECT
  row_number() OVER (ORDER BY id_grupo) AS ROWNUM,
  nome
FROM
  grupos;

I created a fiddle for tests:

http://www.sqlfiddle.com/#! 15/96bea/2/0

  • That’s right, thank you very much, even more for the effort to create the fiddle, I had used the rank() function that Carlos had passed. Thank you very much!

  • The difference is that the rank() will bring the same number if there are equal results in order by that you inform. Example: http://www.sqlfiddle.com/#! 15/e1674/4/0

  • It can also be done with the dense_rank, taking the same care of rank. But in case you use dense_rank with distinct will be the same thing as the row_number.

  • Here are some good examples: https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/

Browser other questions tagged html postgresql

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