Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query to sort data by time and date and then select only the newest record
    primarykey
    data
    text
    <p>I am trying to trying to update create a query that will sort data by Date and time, find the newest record and then update another field in the record marking it as so.</p> <p>Take my life harder the time and data are two separate fields and the time is also a string.</p> <p>So here is what I have so far,</p> <pre><code>UPDATE server.ESCC_HWAY_ASSETS_GULLIES_N set CURRENT_REC = 'Y' From server.ESCC_HWAY_ASSETS_GULLIES_N A inner join ( SELECT GULLY_ID, Max([DATE]) AS MaxDate, MAX([TIME]) AS MaxTime FROM server.ESCC_HWAY_ASSETS_GULLIES_N B GROUP BY GULLY_ID, [DATE] ) B on A.GULLY_ID = B.GULLY_ID and A.[DATE] = B.MaxDate and A.[TIME] = B.MaxTime </code></pre> <p>This results in data that is sorted by time and date but it updates all fields it finds, apart from on dates where there are two entries. Then it only updates the newest record.</p> <p>I am testing on a single record - B47605 which gives the following results on this queuer</p> <pre><code>SELECT GULLY_ID, Max([DATE]) AS MaxDate, MAX([TIME]) AS MaxTime FROM ESMAPADMIN.ESCC_HWAY_ASSETS_GULLIES_N B WHERE GULLY_ID = 'B47605' GROUP BY GULLY_ID, [DATE] Gully_ID MaxDate MaxTime B47605 2008-03-12 00:00:00.000 09:02:29 B47605 2008-09-19 00:00:00.000 09:51:14 B47605 2009-02-16 00:00:00.000 11:18:28 B47605 2009-08-21 00:00:00.000 12:34:45 B47605 2010-03-16 00:00:00.000 09:22:26 B47605 2010-08-25 00:00:00.000 11:19:55 B47605 2011-03-07 00:00:00.000 12:19:56 B47605 2012-05-02 00:00:00.000 20:57:54 </code></pre> <p>The result I would like is to only have the newest record returned so -</p> <pre><code>Gully_ID MaxDate MaxTime B47605 2012-05-02 00:00:00.000 20:57:54 </code></pre> <p>I am not sure how to go from where i am to where i need to be, so any help would be appreciated.</p>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload