none
Alternative to RANK() for SQLCE RRS feed

  • Domanda

  • Hi to all,
    I have this complex query that function properly with SQL SERVER...but if I try to use it with SQL CE, I receive an error on OVER directive of the RANK() command.

    Can anyone help me to modify the query to use it with all SQL platform?

    Thank you in advance.

    This is my query (in bold the error thrown by SQL CE):

    SELECT nodeId, published, newest, documentUser, versionId, text, releaseDate, expireDate, updateDate, templateId FROM cmsDocument WHERE versionID NOT IN(SELECT D.versionId FROM cmsDocument D WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId WHERE DD.nodeId = D.nodeId) AS tmp WHERE tmp.RowNum <= @versionsToKeep OR tmp.published = 1 OR tmp.newest = 1))

    SQL CE - ERROR

    Error Code: 80040E14
    Message : Errore durante l'analisi della query. [ Token line number = 6,Token line offset = 67,Token in error = OVER ]
    Minor Err.: 25501
    Source : SQL Server Compact ADO.NET Data Provider
    Num. Par. : 6
    Num. Par. : 67
    Err. Par. : OVER

    martedì 27 giugno 2017 14:35

Risposte