Extract 2nd Max Date/Value 2

[Forest75]

Hi,

I have a current project which is causing me grief and need help please.

I have a massive list of vehicle maintenance records containing multiple entries against registration numbers and what I need to do is find the latest odometer reading for each Reg. I have written the following query so far which works to a point. However, if a incorrect odometer reading has been entered, say 1000000 miles then I would need the 2nd highest value which would probably be correct. Is there any way of doing this?

Select distinct j.reg_no
,j.work_complete_2
,j.odometer

from vm_001_hdr j

​

inner join (
select
reg_no,
max(odometer) as 'latestodo'
from
vm_001_hdr
group by
reg_no
)
tm on j.reg_no = tm.reg_no and j.odometer = tm.latestodo

where j.odometer > '1'

 

[ElEye]

I think you're looking for MAX from VALUES.

[URL unfurl="true"]http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/20/use-values-clause-to-get-the-maximum-value-from-some-columns-sql-server-t-sql.aspx[/url]



Dave
[

http://flashspot.com

]

 

[gmmastros]

Maybe as simple as:

Select distinct j.reg_no
,j.work_complete_2
,j.odometer

from vm_001_hdr j

inner join (
select
reg_no, 
max(odometer) as 'latestodo'
from 
vm_001_hdr
[!]Where odometer < 1000000[/!]
group by 
reg_no
) 
tm on j.reg_no = tm.reg_no and j.odometer = tm.latestodo

where j.odometer > '1'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

If your explanation of an incorrect odometer reading is as simple as being less than 1 million miles, George's fix should be sufficient. Here's another option using the RANK() function.

;WITH vmRank AS (
SELECT *, RANK() OVER (PARTITION BY reg_no ORDER BY odometer DESC) odoRank
  FROM vm_001_hdr
 WHERE odometer < 1000000
)

SELECT *
  FROM vmRank
 WHERE odoRank = 1

If you want to see the highest and 2nd highest odometer readings for each registration number on a single row and then make some decisions from there (such as is the difference between the 2 values reasonable?), the following query may be of help.

SELECT reg_no,
       MAX(CASE odoRank WHEN 1 THEN odometer END) odometer1st, MAX(CASE odoRank WHEN 1 THEN work_complete_2 END) workComplete1st,
       MAX(CASE odoRank WHEN 2 THEN odometer END) odometer2nd, MAX(CASE odoRank WHEN 2 THEN work_complete_2 END) workComplete2nd
  FROM (SELECT *, RANK() OVER (PARTITION BY reg_no ORDER BY odometer DESC) odoRank FROM vm_001_hdr) v
 GROUP BY reg_no

 

[Forest75]

Thank you everyone for your replies.

Some excellent suggestions how to fix my current issue and more importantly some excellent tips on how to solve other queries.