SQL Query based on Greatest ID

I need a QRY that returns one row for each Truck to show the current active FieldValue.                              
Crirtera                              
1 row for each TruckID                              
Effective Date Less than or equal to GETDATE                        
With the greatest TVID                              
                              
Current Table: TruckValue                              
TVID      FieldID      TruckID      FieldValue      EffectiveDate      
100      614            400            Yes                  2009-10-01 00:00:00      
101      614            400            No                  2010-01-01 00:00:00      
102       614            400            Yes                  2010-01-01 00:00:00      
103       614            400            No                  2010-12-01 00:00:00      
104       614            500            No                  2010-01-01 00:00:00      
105       614            500            Yes                  2010-01-01 00:00:00      
104       614            600            No                  2010-08-01 00:00:00      
                              
Desired Results                              
TVID      FieldID      TruckID      FieldValue      EffectiveDate      
102       614            400            Yes                  2010-01-01 00:00:00      
105       614            500            Yes                  2010-01-01 00:00:00      
                              
Notice TVID's 101 & 102 have the same effective date but different FieldValues.  I need the Greatest TVID returned.                              
mgmeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
SELECT TVID, FieldID, TruckID, FieldValue, EffectiveDate  
FROM ur_table
WHERE FieldValue = 'Yes'
and EffectiveDate  <= getdate()
0
SharathData EngineerCommented:
multiple ways to do this. see the attached.
-- Method 1 
SELECT t1.* 
FROM   your_table AS t1 
       INNER JOIN (SELECT truckid, 
                          Max(tvid) AS tvid 
                   FROM   your_table 
                   WHERE  fieldvalue = 'Yes' 
                          AND effectivedate <= Getdate()) AS t2 
         ON t1.truckid = t2.truckid 
            AND t1.tvid = t2.tvid 

-- Method 2 
SELECT * 
FROM   your_table t1 
WHERE  tvid = (SELECT Max(tvid) 
               FROM   your_table t2 
               WHERE  fieldvalue = 'Yes' 
                      AND effectivedate <= Getdate() 
                      AND t1.truckid = t2.truckid) 

-- Method 3 
SELECT tvid, 
       fieldid, 
       truckid, 
       fieldvalue, 
       effectivedate 
FROM   (SELECT *, 
               Row_number() 
                 OVER(PARTITION BY truckid ORDER BY tvid DESC) rn 
        FROM   your_table 
        WHERE  fieldvalue = 'Yes' 
               AND effectivedate <= Getdate()) AS t1 
WHERE  rn = 1

Open in new window

0
mgmeeAuthor Commented:
I'm sorry, i didn't realize both examples had the FieldValue of "Yes".  The FieldValue can be Yes or No.  When 2 rows have the same EffectiveDate and TruckID, I need the row with the highest TVID number.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this:
SELECT TVID, FieldID, TruckID, FieldValue, EffectiveDate   
FROM ur_table
WHERE TVID IN (
SELECT TVID
FROM ur_table
WHERE FieldValue = 'Yes'
and EffectiveDate  <= getdate()
GROUP By TruckID)

Open in new window

0
mgmeeAuthor Commented:
The WHERE can't be based on FieldValue.  I added another Truck to the table and results to better explain.

Current Table: TruckValue                        
TVID      FieldID      TruckID      FieldValue      EffectiveDate
100      614            400            Yes                  2009-10-01 00:00:00
101      614            400            No                  2010-01-01 00:00:00
102      614            400            Yes                  2010-01-01 00:00:00
103      614            400            No                  2010-12-01 00:00:00
104      614            500            No                  2010-01-01 00:00:00
105      614            500            Yes                  2010-01-01 00:00:00
106      614            600            No                  2010-08-01 00:00:00
107      614            700            Yes                  2010-01-01 00:00:00
108      614            700            No                  2010-01-01 00:00:00

Desired Results                        
TVID      FieldID      TruckID      FieldValue      EffectiveDate
102      614            400            Yes                  2010-01-01 00:00:00
105      614            500            Yes                  2010-01-01 00:00:00
108      614            700            No                  2010-01-01 00:00:00
0
SharathData EngineerCommented:
The row with TruckID = 600 is missed in the expected result. Is that intentional or a miss?
0
mgmeeAuthor Commented:
Yes because the EffectiveDate is greater than GETDATE.
0
SharathData EngineerCommented:
Can you try my queries by removing the filter "FieldValue = 'Yes"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgmeeAuthor Commented:
Method 2 worked great!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.