Captain Treibholz Captain Treibholz - 5 months ago 9
SQL Question

SQL sort a table and update it from top to bottom

I have a list that looks kinda like this:

id Name parentID position
10 Object10 null 1
20 Object20 10 20
30 Object30 10 85
40 Object40 10 48
50 Object40 20 123


Now I want to grab all rows with the parentId
10
and sort them

select * from table1 where parentId=10 ORDER BY id ASC


The List should now look something like this:

id Name parentID position
20 Object20 10 20
30 Object30 10 85
40 Object40 10 48


What I want to do now is change the values in the column
position
. I want to change the numbers so they start from 0 and count up till they arrived at the last element in this sorted list. This should look like this:

id Name parentID position
20 Object20 10 0
30 Object30 10 1
40 Object40 10 2


How can I achieve this?

Answer

Sql Server: For only one parent id:

select ID, Name, ParentID, 
       RowNumber() Over (Order By ID) Position from table1 
   where parentId=10 
   ORDER BY id ASC

For all records partitioned by parentID:

select ID, Name, ParentID, 
       RowNumber() Over (Partition By ParentID Order By ID) Position from table1  
   ORDER BY ParentID, id

Looks like this will work for the following:

CUBRID - DB2 - Firebird - Informix - Oracle - PostgreSQL - SQL Server - Sybase SQL Anywhere - Teradata

https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/

Comments