SELECT DISTINCT on one column (SQL Server 2005 or greater) using php

I have tried the code provided here in *, but i still get an error. can someone tell me where im mistaking? thank you! This is my code:

我已经尝试了*中提供的代码,但我仍然收到错误。谁能告诉我哪里有误?谢谢!这是我的代码:

$allOrdersFromToday = $Microinvest->MSelectList('SELECT * ROW_NUMBER() OVER (PARTITION BY Acct ORDER BY ID) AS RowNumber FROM Operations WHERE Date = "' . $todayDate . '" AND OperType = 2 ORDER BY Acct DESC) AS a', '*', 'a.RowNumber = 1');

which should output as:

应输出为:

SELECT  *
FROM    (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Acct ORDER BY ID) AS RowNumber
         FROM   Operations
         WHERE  Date = "' . $todayDate . '" AND OperType = 2 ORDER BY Acct DESC) AS a
WHERE   a.RowNumber = 1

but im getting an error... :(

但我得到一个错误...... :(

Warning: mssql_query(): message: Incorrect syntax near the keyword 'SELECT'. (severity 15) in /var/www/functions/MssqlLibry.php on line 29

警告:mssql_query():message:关键字“SELECT”附近的语法不正确。 (严重级15)在第29行的/var/www/functions/MssqlLibry.php中

1 个解决方案

#1

SQL Server does not support order by in subqueries, under most circumstances. Try this:

在大多数情况下,SQL Server不支持子查询中的顺序。试试这个:

SELECT  a.*
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY Acct ORDER BY ID) AS RowNumber
      FROM Operations o
      WHERE  Date = "' . $todayDate . '" AND
             OperType = 2
     ) a
WHERE a.RowNumber = 1
ORDER BY a.Acct DESC;

In addition, you could have a problem because of the format of the date. You should use parameterized queries rather than substituting values into strings.

此外,由于日期的格式,您可能会遇到问题。您应该使用参数化查询,而不是将值替换为字符串。

#1