Select the latest record for an Id per day - Oracle pl sql
How can I write a sql statement which returns the latest record per day, per an Id. Eg. with the data as below.
Id Name Comment Value DateTime 1 Tim Test 100 02/06/2020 15:05:12 2 Sue House 200 03/06/2020 08:25:01 1 Tim Test 150 02/06/2020 18:05:12 3 Doug Cars 680 10/05/2019 04:45:10 2 Sue Tennis 200 03/06/2020 10:35:15
I'd get :
Id Name Comment Value DateTime 1 Tim Test 150 02/06/2020 18:05:12 3 Doug Cars 680 10/05/2019 04:45:10 2 Sue Tennis 200 03/06/2020 10:35:15
Would I need a sub-select query which groups by the max date time?
A window function would be the approach I would take:
select id, name, comment, value, dateTime from ( select id, name, comment, value, dateTime , last_value(dateTime) over( partition by id, trunc(datetime) order by dateTime rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) lv ) where dateTime=lv
Oracle select most recent date record, you can't use aliases from select list inside the WHERE clause (because of the Order of Evaluation of a SELECT statement). also you cannot� This method is very flexible. For example, if you need to select many columns from the row with the latest term, then it's easy to code (just add the column names in the main query), and fast to run (no additional charge for each column). Or say you need to handle ties differently, or someone asks you to find the 5 latest terms for each priority.
One simple way to do it is to filter with a correlated subquery:
select t.* from mytable t where t.datetime = ( select max(t1.datetime) from mytable t1 where t1.datetime >= trunc(t.datetime) and t1.datetime < trunc(t.datetime) + 1 )
You might also like then anti-
left join approach:
select t.* from mytable t left join mytable t1 on t1.datetime > t.datetime and t1.datetime >= trunc(t.datetime) and t1.datetime < trunc(t.datetime) + 1 where t1.id is null
Ask TOM "SQL query to get the most recent entry from a , SQL query to get the most recent entry from a group. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey! FROM (SELECT htenant, hmy, sactiontype2a, dteffective2b, Connor and Chris don't just spend all day on AskTOM. A common query that crops up in web apps is finding the oldest or the most recent record in a single table. This is straightforward in SQL. You can even write the relevant part of the query without knowing anything about the table (other than the fact that it has a timestamp column called created_at):
By using FIRST/LAST you don't need even a sub-query:
WITH t(ID, NAME, COMMENT_, VALUE, DateTime) AS ( SELECT 1,'Tim','Test', 100, TO_DATE('02/06/2020 15:05:12', 'dd/mm/yyyy hh24:mi:ss') FROM dual UNION ALL SELECT 2,'Sue','House', 200, TO_DATE('03/06/2020 08:25:01', 'dd/mm/yyyy hh24:mi:ss') FROM dual UNION ALL SELECT 1,'Tim','Test', 150, TO_DATE('02/06/2020 18:05:12', 'dd/mm/yyyy hh24:mi:ss') FROM dual UNION ALL SELECT 3,'Doug','Cars', 680, TO_DATE('10/05/2019 04:45:10', 'dd/mm/yyyy hh24:mi:ss') FROM dual UNION ALL SELECT 2,'Sue','Tennis', 300, TO_DATE('03/06/2020 10:35:15', 'dd/mm/yyyy hh24:mi:ss') FROM dual UNION ALL SELECT 2,'Sue','Steets', 400, TO_DATE('10/10/2020 10:35:15', 'dd/mm/yyyy hh24:mi:ss') FROM dual) SELECT ID, MAX(NAME) KEEP (DENSE_RANK LAST ORDER BY DateTime) AS NAME, MAX(COMMENT_) KEEP (DENSE_RANK LAST ORDER BY DateTime) AS COMMENT_, MAX(VALUE) KEEP (DENSE_RANK LAST ORDER BY DateTime) AS VALUE, MAX(DateTime) KEEP (DENSE_RANK LAST ORDER BY DateTime) AS DateTime FROM t GROUP BY ID, TRUNC(DateTime); +------------------------------------------+ |ID|NAME|COMMENT_|VALUE|DATETIME | +------------------------------------------+ |1 |Tim |Test |150 |02.06.2020 18:05:12| |2 |Sue |Tennis |300 |03.06.2020 10:35:15| |2 |Sue |Steets |400 |10.10.2020 10:35:15| |3 |Doug|Cars |680 |10.05.2019 04:45:10| +------------------------------------------+
Ask TOM "Fetching last record from a table", Are there any built-ins in report builder which can fetch the last record of a table? tkyte@TKYTE816> select x from t where rownum = 1 order by rowid desc; X results! once more you made the theory behind sql and database-concepts clear! here i dint use the row id BBB again. i dint because when i was going through� SQL MAX() on date value: Here we have discussed the usage of SQL MAX() function on date type of column of a table. SQL WHERE clause along with the SQL MAX() can be used as a subquery to find the maximum value of a column based upon some condition.
Retrieve the id of the last inserted row with some - Ask Tom, Retrieve the id of the last inserted row with some combination of values Consequently the select and the insert operations have become intolerably slow and deteriorating by the day. In order to improve it I was thinking of putting the retrieve last id function in a PL/SQL block or even a before_insert trigger� PL/SQL records are similar in structure to a row in a database table. A record consists of components of any scalar, PL/SQL record, or PL/SQL table type. It makes your life easier by transferring the entire row into a record, rather than transferring each column into a variable separately. PL/SQL supports three kinds of records: table-based
Ask TOM "getting rows N through M of a result set", Can I do using SQL Plus ? and we said In Oracle8i, release 8.1 -- yes. select * CREATE TYPE <type_name_db> IS RECORD ( <column 1> <datatype>, ); In the first syntax, we can see the keyword 'CREATE TYPE' this instructs the compiler to create the record type named "type_name_db" with the specified column as a database object.
Here are two tables. SCHOOL_STAFF. SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID ===== ABE Principal 24-JAN-13 111222 ABE Principal 09-FEB-12 222111
- (1) What is your Oracle version? Different answers are available depending on that. (2) Why the
plsqltag? (3) Most importantly: Can there be ties? Where there are two or more rows for the same Id and the same date, with exactly the same time-of-day component, all tied for "latest" in that goupr? If so, how should that be handled? Return ALL the rows tied for "latest record" for that person and date? Return only one of them, and if so, which one? (Or will "any one of the tied ones" work equally well?)
- This does not group "per day, per ID"
- @WernfriedDomscheit; you are correct. The window function needs an additional PARTITION BY trunc(DateTime). I have edited my answer above.
- Disadvantage of these methods if having to scan the table twice. Could be a performance problem if the table is large
- @BobC: one way or another, you need to scan more than once (analytics functions do not really differ in that matter).
- @GMB - I don't know why you believe that, but you are definitely wrong. Analytic functions were developed specifically with that improvement as the goal. The data may be processed more than once (inner query, outer query) but the stored data is read from disk only once.
- @gmb, run an explain plan on the two solutions and you will see the number of scans. Also, I do not think your solution gives the desired results; the original question requires the results to be grouped/partitioned by ID.