0

I have a table videos with the columns id | position | title now I want to sort the column position from the end to the bottom in a permanent way with e.g. UPDATE. I could change that manually but there are more than 460 rows I would have to update manually which is really annoying.

I already tried 

SELECT position FROM videos ORDER BY position + 0 asc;

which shows the needed result but I don't know how to make it a permanent change. If I use 

UPDATE videos SET position = (SELECT position FROM videos ORDER BY position + 0 asc);

which changes the rows of the column to one value.

The table looks like this 

Id    | position | title
---------------------------
tz7e  | 3        | title1
-cQ6  | 4        | title2
Q1L3  | 1        | title3
y456  | 2        | title4
V2n4  | 5        | title5
p76b  | 7        | title6
98kN  | 6        | title7

But it want to have it like that

Id    | position | title
---------------------------
tz7e  | 7        | title1
-cQ6  | 6        | title2
Q1L3  | 5        | title3
y456  | 4        | title4
V2n4  | 3        | title5
p76b  | 2        | title6
98kN  | 1        | title7

But with 460 entries in the column position that I want to rerow.

error666
  • 3
  • 3
  • shample input and output is helpfull – Zaynul Abadin Tuhin Mar 26 '19 at 08:55
  • 2
    Why bother? Reorder rows is a bad idea. – jarlh Mar 26 '19 at 08:55
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Mar 26 '19 at 08:56
  • What's the intention of the `+ 0`? Can't you simply use `ORDER BY position ASC`? –  Mar 26 '19 at 08:59
  • If I use `ORDER BY position asc` the result get sort like this 0,1,10,11,12 and so on but want to have it like 1,2,3,4,5,... – error666 Mar 26 '19 at 09:05
  • @jarlh I want to change the value of the column because the titles got mixed up and now I want to get them in the right order as they were before. – error666 Mar 26 '19 at 09:07
  • Show us some sample table data - both current and wanted versions. All as formatted text, not images. – jarlh Mar 26 '19 at 09:09
  • "want to have it like 1,2,3,4,5,..." - not exactly sure what you want but I think this is a data type issue. Sorting strings that contain numbers is different than sorting numerics. – nicomp Mar 26 '19 at 09:19
  • Edit: Updated question with example table – error666 Mar 26 '19 at 11:51
  • Are you looking to re-order the position based on the order of which they were recorded? what determined the correct order? – jimmy8ball Mar 26 '19 at 12:55
  • the order before was like the one in the second table if a new row was added the column `position` counted + 1. In the example above there would be a new row which now has the number 1 and the other entries of `position` would get + 1. – error666 Mar 26 '19 at 13:10
  • But do you have a datetime recorded when the new value was added? Could you not use this to re-sequence the positions? – jimmy8ball Mar 26 '19 at 13:47
  • There is no datetime record. I am really lost as there is no solution to rearrange the mixed up entries in the table – error666 Mar 29 '19 at 11:37
  • If the idea is to get back to the previous ordering, then unless you have something that determined the orginal sequence, you will have to undertake this manually. In future it is worth structuring the data correctly. – jimmy8ball Mar 29 '19 at 11:43

1 Answers1

0

Sounds to me like what you want is a Clustered Index. Put it on the column Position and the rows will be physically ordered by that column on the disk. The order ascending or descending does not matter in this case.

CREATE CLUSTERED INDEX name ON videos (position);
Kars
  • 845
  • 1
  • 14
  • 33