2235023 - ORDER BY DESC could return incorrect order -- SAP IQ | SAP Knowledge Base Article

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 ONE Support launchpad (Login required).

Search for additional results

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