CODEWITHSUNDEEP
sqlperformancesql-order-bywhere-clause
Ben
Ben

Reputation: 31

Alternative for order by DESC

It takes total 9mins for this sql query to fetch records..

select top 1 checkdate 
from BTHI1
where CUSTOMERID = 'AUTOMO' and recordtype='T'
order by checkdate desc

any other way it can reduced the query time?

Upvotes: 0

Views: 1149

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270341

For this query:

select top 1 checkdate 
from BTHI1
where CUSTOMERID = 'AUTOMO' and recordtype = 'T'
order by checkdate desc

You want an index on BTH1(CUSTOMERID, recordtype, checkdate DESC). The first two columns can be in either order.

Note that you can also write this as:

select max(checkdate) 
from BTHI1
where CUSTOMERID = 'AUTOMO' and recordtype = 'T';

Upvotes: 1

Related Questions