We help IT Professionals succeed at work.

Rewrite SQL Statement for Most Recently Updated Rows

diablo089
diablo089 used Ask the Experts™
on
How can I rewrite the following SQL statement so that I only get the rows that have the most recent update?

ie. I have:
WName1  FmName1  1000  6/29/2010
WName1  FmName1  1026  6/30/2010

I only want to see "WName1  FmName1  1026  6/30/2010" returned

SELECT CD_WELL.well_common_name, CD_WELLBORE_FORMATION.formation_name, CD_WELLBORE_FORMATION.prognosed_md + {fn IFNULL(CD_DATUM.datum_elevation, 0)}, CD_WELLBORE_FORMATION.update_date FROM CD_WELL, CD_WELLBORE_FORMATION, CD_SITE, CD_DATUM WHERE (UPPER(CD_SITE.site_name) LIKE UPPER('%" & TextPadName.Text & "%')) AND (CD_WELL.well_id = CD_WELLBORE_FORMATION.well_id) AND (CD_WELL.site_id = CD_SITE.site_id) AND (( ( {fn UCASE( CD_DATUM.is_default )} = 'Y' ) OR ( CD_DATUM.datum_id IS NULL ) OR ( {fn CONCAT (CD_DATUM.well_id, CD_DATUM.datum_id)} IN ('03q2DecAl4nlEgX', '86zNT2KSuII6yfH', '8drumnJseey3L5f', '966uBIK5Xhtos05', 'aaIx5gNxghWGqO0', 'AXm50zSOPrgZsZ8', 'dOBDPIleVGuPdj9', 'e2qQjvwmitACVhA', 'e7aRS7VfTwfciQ1', 'FUtaJlUXErY5Qiy', 'iOVCMPIgTeX7Dub', 'JC1Rm8EZ9HmGttJ', 'lsZtTPuanusLIL9', 'ntfL8vG8VsoG82r', 'rvPgl6F83dhPqcZ', 'RwGi6bVLL6W2d2i', 'SJog0u94MuVqjWN', 'sSKqoFU4uaSbrde', 'uttJBp2CsFxWI9W', 'uWUtReyLy1h2WFd', 'wJbDqiXc1wcEPQw', 'ww39g31VygDulen', 'YWwMjlUFn0WNMVP', 'ZILDpIPYMHfWqVj') )) AND (CD_WELL.well_id = CD_DATUM.well_id)) ORDER BY 4 DESC

Open in new window


SELECT CD_WELL.well_common_name, CD_WELLBORE_FORMATION.formation_name, CD_WELLBORE_FORMATION.prognosed_md + {fn IFNULL(CD_DATUM.datum_elevation, 0)}, CD_WELLBORE_FORMATION.update_date FROM CD_WELL, CD_WELLBORE_FORMATION, CD_SITE, CD_DATUM WHERE (UPPER(CD_SITE.site_name) LIKE UPPER('%" & TextPadName.Text & "%')) AND (CD_WELL.well_id = CD_WELLBORE_FORMATION.well_id) AND (CD_WELL.site_id = CD_SITE.site_id) AND (( ( {fn UCASE( CD_DATUM.is_default )} = 'Y' ) OR ( CD_DATUM.datum_id IS NULL ) OR ( {fn CONCAT (CD_DATUM.well_id, CD_DATUM.datum_id)} IN ('03q2DecAl4nlEgX', '86zNT2KSuII6yfH', '8drumnJseey3L5f', '966uBIK5Xhtos05', 'aaIx5gNxghWGqO0', 'AXm50zSOPrgZsZ8', 'dOBDPIleVGuPdj9', 'e2qQjvwmitACVhA', 'e7aRS7VfTwfciQ1', 'FUtaJlUXErY5Qiy', 'iOVCMPIgTeX7Dub', 'JC1Rm8EZ9HmGttJ', 'lsZtTPuanusLIL9', 'ntfL8vG8VsoG82r', 'rvPgl6F83dhPqcZ', 'RwGi6bVLL6W2d2i', 'SJog0u94MuVqjWN', 'sSKqoFU4uaSbrde', 'uttJBp2CsFxWI9W', 'uWUtReyLy1h2WFd', 'wJbDqiXc1wcEPQw', 'ww39g31VygDulen', 'YWwMjlUFn0WNMVP', 'ZILDpIPYMHfWqVj') )) AND (CD_WELL.well_id = CD_DATUM.well_id)) ORDER BY 4 DESC
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ORDER BY date DESC LIMIT 1

Commented:
SELECT top 1 CD_WELL.well_common_name, CD_WELLBORE_FORMATION.formation_name, CD_WELLBORE_FORMATION.prognosed_md + {fn IFNULL(CD_DATUM.datum_elevation, 0)}, CD_WELLBORE_FORMATION.update_date FROM CD_WELL, CD_WELLBORE_FORMATION, CD_SITE, CD_DATUM WHERE (UPPER(CD_SITE.site_name) LIKE UPPER('%" & TextPadName.Text & "%')) AND (CD_WELL.well_id = CD_WELLBORE_FORMATION.well_id) AND (CD_WELL.site_id = CD_SITE.site_id) AND (( ( {fn UCASE( CD_DATUM.is_default )} = 'Y' ) OR ( CD_DATUM.datum_id IS NULL ) OR ( {fn CONCAT (CD_DATUM.well_id, CD_DATUM.datum_id)} IN ('03q2DecAl4nlEgX', '86zNT2KSuII6yfH', '8drumnJseey3L5f', '966uBIK5Xhtos05', 'aaIx5gNxghWGqO0', 'AXm50zSOPrgZsZ8', 'dOBDPIleVGuPdj9', 'e2qQjvwmitACVhA', 'e7aRS7VfTwfciQ1', 'FUtaJlUXErY5Qiy', 'iOVCMPIgTeX7Dub', 'JC1Rm8EZ9HmGttJ', 'lsZtTPuanusLIL9', 'ntfL8vG8VsoG82r', 'rvPgl6F83dhPqcZ', 'RwGi6bVLL6W2d2i', 'SJog0u94MuVqjWN', 'sSKqoFU4uaSbrde', 'uttJBp2CsFxWI9W', 'uWUtReyLy1h2WFd', 'wJbDqiXc1wcEPQw', 'ww39g31VygDulen', 'YWwMjlUFn0WNMVP', 'ZILDpIPYMHfWqVj') )) AND (CD_WELL.well_id = CD_DATUM.well_id)) ORDER BY
CD_WELLBORE_FORMATION.update_date DESC

see the text in bold

Author

Commented:
tvPrasad,

 I think your method will give me the single row with the most recent update. I guess I should have been more clear what I wanted. I've got several different CD_WELLBORE_FORMATION.formation_name values for each CD_WELL.well_common_name. I want to see all of the distinct  CD_WELLBORE_FORMATION.formation_name values for each CD_WELL.well_common_name, but only those with the most recent CD_WELLBORE_FORMATION.update_date.

ie - I have:

WName1  FmName1  1000  6/29/2010
WName1  FmName1  1026  6/30/2010
WName1  FmName2  1048  6/29/2010
WName1  FmName2  1256  6/30/2010
WName1  FmName3  2034  6/29/2010
WName1  FmName3  2156  6/30/2010

I would like to see:

WName1  FmName1  1026  6/30/2010
WName1  FmName2  1256  6/30/2010
WName1  FmName3  2156  6/30/2010
Have you tried the group by and having parameters?

Not sure it will work, don't have data to test on.

GROUP BY Column1Name, Column1Name HAVING MAX(dateColumn);
Top Expert 2012
Commented:
Try it like this:
SELECT  CD_WELL.well_common_name,
        CD_WELLBORE_FORMATION.formation_name,
        CD_WELLBORE_FORMATION.prognosed_md + {fn IFNULL(CD_DATUM.datum_elevation, 0)},
        CD_WELLBORE_FORMATION.update_date
FROM    CD_WELL
        INNER JOIN CD_WELLBORE_FORMATION ON CD_WELL.well_id = CD_WELLBORE_FORMATION.well_id
        INNER JOIN (
				SELECT	well_id,
						MAX(update_date) Latestupdate_date
				FROM	CD_WELLBORE_FORMATION
				GROUP BY
						well_id) CD_WELLBORE_FORMATION2 ON CD_WELLBORE_FORMATION.well_id = CD_WELLBORE_FORMATION2.well_id
												AND CD_WELLBORE_FORMATION.update_date = CD_WELLBORE_FORMATION2.Latestupdate_date
        INNER JOIN CD_SITE ON CD_WELL.site_id = CD_SITE.site_id
        INNER JOIN CD_DATUM ON CD_WELL.well_id = CD_DATUM.well_id
WHERE   UPPER(CD_SITE.site_name) LIKE UPPER('%" & TextPadName.Text & "%')
        AND ((({fn UCASE(CD_DATUM.is_default)} = 'Y')
              OR CD_DATUM.datum_id IS NULL
              OR ({fn CONCAT (CD_DATUM.well_id, CD_DATUM.datum_id)} IN ('03q2DecAl4nlEgX', '86zNT2KSuII6yfH', '8drumnJseey3L5f', '966uBIK5Xhtos05',
                                                                        'aaIx5gNxghWGqO0', 'AXm50zSOPrgZsZ8', 'dOBDPIleVGuPdj9', 'e2qQjvwmitACVhA',
                                                                        'e7aRS7VfTwfciQ1', 'FUtaJlUXErY5Qiy', 'iOVCMPIgTeX7Dub', 'JC1Rm8EZ9HmGttJ',
                                                                        'lsZtTPuanusLIL9', 'ntfL8vG8VsoG82r', 'rvPgl6F83dhPqcZ', 'RwGi6bVLL6W2d2i',
                                                                        'SJog0u94MuVqjWN', 'sSKqoFU4uaSbrde', 'uttJBp2CsFxWI9W', 'uWUtReyLy1h2WFd',
                                                                        'wJbDqiXc1wcEPQw', 'ww39g31VygDulen', 'YWwMjlUFn0WNMVP', 'ZILDpIPYMHfWqVj'))
             )
            )
ORDER BY 4 DESC

Open in new window

Author

Commented:
That's exactly what I was looking for. Works great. Thanks acperkins!
Top Expert 2012

Commented:
Caveat, if there are two with exactly the same date, then both will be returned.  If that is a problem, then you may have to resoirt to using a CTE.