Insert ranking into column

Question

i have a following PREFERENCE_SCORE table

ID(int) | SCORE(Double) | RANKING(int) |

I want to insert into database using the following code

String sql = "INSERT INTO PREFERENCE_SCORE_ ( ID, 
              SCORE,RANK) VALUES (?,?,?) ";
String query ="SELECT ID,FPIS,FNIS FROM db_housekeep";
ResultSet rsp = stats.executeQuery(query);
while(rsp.next()){
    int id = rsp.getInt(1);
    double fpis = rsp.getDouble(2);
    double fnis = rsp.getDouble(3);
    prSt.setInt(1, id);
    prSt.setDouble(2, fnis/(fnis+fpis));
    prSt.setInt(3, THIS PART i dont understand)
    prSt.executeUpdate();
}

What should i add so the RANKING column is automatically filled by the rank of the data according to the SCORE column?

And what would be the numerical relation between the two columns? — FDavidov, May 07 '18 at 07:32
So, you want to everytime update whole Ranking column when score gets inserted to get the rank based on the score? — Harshit, May 07 '18 at 07:33
Are you sure you want to write it directly in the table? Maybe it would be better to get rank by query, no? — Valentin Michalak, May 07 '18 at 07:34
the id from db_housekeep are the foreign key to id from preference_score @MạnhQuyếtNguyễn — DonQuixote, May 07 '18 at 07:39
Like this ? https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table — Valentin Michalak, May 07 '18 at 07:41
@DonQuixote, you should better use `select` query and get it order by SCORE DESC to get the ranking — Harshit, May 07 '18 at 07:41

score 0 · Accepted Answer · answered May 07 '18 at 08:02

0

I think this approach is not beneficial. It will generate ambiguous data for rank. e.g. if we insert the first row with (score 99) the rank will be 1 and after it, we insert the second row with score 100 what is the rank? (according to runtime calculation rant should 1 but we have already 1 so what can we do?). So, its prefer to use it only will select data from the table for showing.

Hopefully, you know the syntax as ( RANK ( ) OVER ( [ partition_by_clause ] order_by_clause ) )

answered May 07 '18 at 08:02

M Danish

480
2
5

1

Derby does not support window functions – May 07 '18 at 08:03
If you want still sale workflow then we have a workaround that is efficiently costly – M Danish May 07 '18 at 08:04
@MDanish if the table is just `id` and `score` and i want to show the rank in jTable, I had to use the `select` query? – DonQuixote May 07 '18 at 08:06
Check this. it may help.https://stackoverflow.com/questions/35778746/query-to-rank-rows-in-groups – M Danish May 07 '18 at 08:11

Insert ranking into column

1 Answers1