asked on

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.

Raja Jegan R

SELECT TVID, FieldID, TruckID, FieldValue, EffectiveDate
FROM ur_table
WHERE FieldValue = 'Yes'
and EffectiveDate <= getdate()

Sharath S

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

mgmee

ASKER

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.

Raja Jegan R

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

mgmee

ASKER

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

Sharath S

The row with TruckID = 600 is missed in the expected result. Is that intentional or a miss?

mgmee

ASKER

Yes because the EffectiveDate is greater than GETDATE.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mgmee

ASKER

Method 2 worked great!!