1

It doesn't look like SQL Server Compact Edition supports the RANK() function. (See Functions (SQL Server Compact Edition) at http://msdn.microsoft.com/en-us/library/ms174077(SQL.90).aspx).

How would I duplicate the RANK() function in a SQL Server Compact Edition SELECT statement.

(Please use Northwind.sdf for any sample select statements, as it is the only one I can open with SQL Server 2005 Management Studio.)

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
AMissico
  • 21,470
  • 7
  • 78
  • 106

2 Answers2

1

Use:

  SELECT x.[Product Name], x.[Unit Price], COUNT(y.[Unit Price]) AS Rank 
    FROM Products x
    JOIN Products y ON x.[Unit Price] < y.[Unit Price] 
                  OR (    x.[Unit Price]=y.[Unit Price] 
                      AND x.[Product Name] = y.[Product Name]) 
GROUP BY x.[Product Name], x.[Unit Price] 
ORDER BY x.[Unit Price] DESC, x.[Product Name] DESC;

Previously:

SELECT y.id,
       (SELECT COUNT(*)
         FROM TABLE x
        WHERE x.id <= y.id) AS rank
  FROM TABLE y
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Not working. "There was an error parsing the query. [ Token line number = 2,Token line offset = 9,Token in error = SELECT ]" – AMissico Jun 11 '10 at 21:49
1
SELECT x.[Product Name], x.[Unit Price], COUNT(y.[Unit Price]) Rank 
FROM Products x, Products y 
WHERE x.[Unit Price] < y.[Unit Price] or (x.[Unit Price]=y.[Unit Price] and x.[Product Name] = y.[Product Name]) 
GROUP BY x.[Product Name], x.[Unit Price] 
ORDER BY x.[Unit Price] DESC, x.[Product Name] DESC;

Solution modified from Finding rank of the student -Sql Compact at Finding rank of the student -Sql Compact

Community
  • 1
  • 1
AMissico
  • 21,470
  • 7
  • 78
  • 106
  • +1: Well done, good find. I updated my answer to include an ANSI-92 JOIN version; what you posted is ANSI-89 join syntax. – OMG Ponies Jun 11 '10 at 23:21