Clause similar to Mysql LIMIT in MSSQL

Asked

Viewed 168 times

5

I need to run the query below in PHP with SQL Server, however I cannot use the clause LIMIT absent in darlings microsoft.

$SqlTabelaAtual="SELECT * 
         FROM BusinessCadTabPreco
         RIGHT JOIN BusinessCadTabPrecoItem ON BusinessCadTabPreco.CdTabela = BusinessCadTabPrecoItem.CdTabela
         WHERE  BusinessCadTabPreco.CdEmpresa =01
         AND CdProduto =".$row['CdProduto']."
         ORDER BY  BusinessCadTabPreco.DtSincronizar DESC LIMIT 1
php sql sql-server syntax select-limit

  • Which version of mssql?

  • I believe it is 2015, I ran a php_info, I didn’t see anything in mssql info. How can I test?

2 answers

6


SQL Server uses the syntax TOP:

$SqlTabelaAtual = "SELECT TOP 1 * 
         FROM BusinessCadTabPreco
         RIGHT JOIN BusinessCadTabPrecoItem ON BusinessCadTabPreco.CdTabela = BusinessCadTabPrecoItem.CdTabela
         WHERE  BusinessCadTabPreco.CdEmpresa =01
         AND CdProduto =".$row['CdProduto']."
         ORDER BY  BusinessCadTabPreco.DtSincronizar DESC"

I put in the Github for future reference.

The 2012 and later version allows ANSI syntax with OFFSET FETCH.

  • From which version works withTOP at the end of the consultation?

  • 1

    If I’m not mistaken 2005. No one should be using an earlier version.

  • Good thing that SQL Server 2000 is no longer my problem :D, because it is suffered ... I think they haven’t retired it yet.

  • I agree, is that I thought it was something recent, I tested here did not work, I think TOP in the sql-server It always comes after the selectnot at the end of the query. @bigown

  • 1

    @rubStackOverflow Oops, you’re right, fixed :)

5

Depending on the case TOP is suitable to limit lines of a query, in newer versions there are other more flexible clashes.

SELECT TOP 10 * FROM tabela.

Related:

Paging results in SQL Server 2000

  • Thanks @rray that helped !

Browser other questions tagged php sql sql-server syntax select-limit

You are not signed in. Login or sign up in order to post.