CODEWITHSUNDEEP
sqlhana
user2233777
user2233777

Reputation: 49

SQL order by depending on multiple column according to a threshold

I am using SAP HANA so there is a few part of SQL language that cannot be used(cte table, a few keywords...). I use a query that is giving me results associated with two scores, rows are sorted according to the first score column, and then to the second score column. I have a lot of rows that have a small difference for first score, and a large difference for second score, so i would like to order queries according to the second score, but only when the difference between a row and the next row doesnt exceed a threshold. The score2 can be null.

For now, my code looks like that :

select 
      "name",
      "score1",
      "score2",
      LEAD("score1", 1) over (order by "score1" desc, "score2" desc) as "nextscore1"
from
      //myrequest generating results
group by
      "name",
      "score1",
      "score2"
order by 
      case when ("nextscore1" IS NOT NULL) AND ("score1" - "nextscore1" < 0,1) then "score2" else "score1" END DESC,
      case when ("nextscore1" IS NOT NULL) AND ("score1" - "nextscore1" < 0,1) then "score1" else "score2" END DESC

It always output me results, sorted according to one and then the other column, but not the mixed sort i would expect. Thanks for your help.

Upvotes: 0

Views: 2535

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

Not sure where you went wrong here, but for me this works:

create column table scores (name varchar(10), score1 decimal (10,4), score2 decimal (10,4)) 

insert into scores values ('v1', 1, NULL);
insert into scores values ('v2', 1.5, 2.2);
insert into scores values ('v3', 1.51, 2.4);
insert into scores values ('v4', 1.6, 2.1);
insert into scores values ('v5', 1.4, 2.78);
insert into scores values ('v6', -1.2, -2.1);
insert into scores values ('v7', -1.1, 2.1);
insert into scores values ('v8', 1, 2);


select name, score1, score2, nextscore1,
      case 
           when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
           then score2 
           else score1 
      end score_sort1,
      case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1) 
           then score1 
           else score2 
      end score_sort2
from
    (select 
          name,
          score1,
          score2,
          LEAD(score1, 1) over (order by score1 desc, score2 desc) as     nextscore1
    from
        scores
    group by
          name,
          score1,
          score2)
ORDER BY 
      case 
           when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
           then score2 
           else score1 
      end ,  
      case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1) 
           then score1 
           else score2 
      end ;

To illustrate I put the case expressions also into the selection list.

NAME    SCORE1  SCORE2  NEXTSCORE1  SCORE_SORT1 SCORE_SORT2
v6      -1.2000 -2.1000 ?           -1.2000     -2.1000    
v7      -1.1000 2.1000  -1.2000     -1.1000     2.1000     
v1      1.0000  ?       -1.1000     1.0000      ?          
v5      1.4000  2.7800  1.0000      1.4000      2.7800     
v2      1.5000  2.2000  1.4000      1.5000      2.2000     
v8      1.0000  2.0000  1.0000      2.0000      1.0000     << small diff of score1 and nextscore1 => score2 is used for SCORE_SORT1
v4      1.6000  2.1000  1.5100      2.1000      1.6000     << small diff of score1 and nextscore1 => score2 is used for SCORE_SORT1 
v3      1.5100  2.4000  1.5000      2.4000      1.5100     << small diff of score1 and nextscore1 => score2 is used for SCORE_SORT1

Upvotes: 0

Related Questions