SAP Knowledge Base Article - Preview

2235023 - ORDER BY DESC could return incorrect order -- SAP IQ

Symptom

  • ORDER BY DESC could incorrectly order the result sets in either of the following situations:
  • SQL could be sufficiently complex but with no join, with no analytical function and with just an ORDER BY DESC.
  • SQLs with analytical ranking and aggregate functions involving OVER PARTITION ORDER BY could return wrong ranking, such as:
    • RANK OVER PARTITION BY ORDER BY
    • DENSE_RANK OVER PARTITION BY ORDER BY 
    • NTILE OVER PARTITION BY ORDER BY
    • ROW_NUMBER OVER PARTITION BY ORDER BY
    • PERCENT_RANK OVER PARTITION BY ORDER BY
    • WEIGHTED_AVG OVER PARTITION BY ORDER BY
    • LAG OVER PARTITION BY ORDER BY
    • LEAD OVER PARTITION BY ORDER BY
  • The issue is more likely to occur when there are:
    • PARTITION BY X ORDER BY Y, Z DESC
    • PARTITION BY X, Y ORDER BY Z DESC
    • at least one column is ordered by DESCENDING in the ORDER BY clause
    • a join between 1 or more tables, one of the join column is in the PARTITION BY clause 


Read more...

Environment

  • SAP IQ 15.x
  • SAP IQ 16

Product

SAP IQ 16.0 ; SAP Sybase IQ 15.2 ; SAP Sybase IQ 15.4

Keywords

KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.