?
Solved

SQL Query based on Greatest ID

Posted on 2010-03-24
9
Medium Priority
?
292 Views
Last Modified: 2012-05-09
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.                              
0
Comment
Question by:mgmee
  • 4
  • 3
  • 2
9 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28463024
SELECT TVID, FieldID, TruckID, FieldValue, EffectiveDate  
FROM ur_table
WHERE FieldValue = 'Yes'
and EffectiveDate  <= getdate()
0
 
LVL 41

Expert Comment

by:Sharath
ID: 28463769
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
 

Author Comment

by:mgmee
ID: 28466975
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28467442
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
 

Author Comment

by:mgmee
ID: 28469166
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
 
LVL 41

Expert Comment

by:Sharath
ID: 28470830
The row with TruckID = 600 is missed in the expected result. Is that intentional or a miss?
0
 

Author Comment

by:mgmee
ID: 28472227
Yes because the EffectiveDate is greater than GETDATE.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 28473682
Can you try my queries by removing the filter "FieldValue = 'Yes"
0
 

Author Closing Comment

by:mgmee
ID: 31706662
Method 2 worked great!!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question