Link to home
Start Free TrialLog in
Avatar of websss
websssFlag for Kenya

asked on

sql min max

Hi

What is the most performant way to select the min and max value

here is my query

select (min odometer) and (max odometer) 
From MyTable
Where gpsDateTime between '2018-01-01' and '2018-01-17' and eventID = 1

Open in new window


i basically need  to return 2 item, the max odometer and min odometer reading based on the datetime
I'm looking for the most efficient SQL that can achieve this

SQL server 2014
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ste5an
ste5an
Flag of Germany image
First of all: Use correct SQL. This includes using an save date literal format.

SELECT MIN(odometer) AS min_odometer ,
       MAX(odometer) AS max_odometer
FROM   MyTable
WHERE  gpsDateTime
       BETWEEN '20180101' AND '20180117'
       AND eventID = 1;

Open in new window


Performance depends on correct indices. Thus you need a combined index over (eventID, gpsDateTime, odometer) to cover your query.
Avatar of websss
websss
Flag of Kenya image

ASKER

sorry ste5an awarded too soon