INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Extract 2nd Max Date/Value

Extract 2nd Max Date/Value

(OP)
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'

RE: Extract 2nd Max Date/Value

Maybe as simple as:

CODE

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

RE: Extract 2nd Max Date/Value

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.

CODE

;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.

CODE

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 

RE: Extract 2nd Max Date/Value

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close