0

I'm trying to figure out how to make a query show me only when an object has touched a specific systemic location based on time stamp. For example:

Query:
SELECT
    id,
    timestamp,
    lp,
    location
FROM
    unit_location
WHERE
    lp IN (
        '1234',
        '9999');

id          timestamp           lp      location
11110000    11-APR-23 15:05:31  1234    Location 2
22220000    11-APR-23 15:22:52  1234    Location 1
33330000    11-APR-23 15:35:10  1234    Location 2
44440000    11-APR-23 15:04:11  9999    Location 2
55550000    11-APR-23 19:29:04  9999    Location 1
66660000    11-APR-23 19:40:14  9999    Location 2

How would I make it so that a query could run and it would only bring back the data for the last timestamp but only if its location 2 for each individual lp?

Example here, it would only show me ID:

id          timestamp           lp      location
33330000    11-APR-23 15:35:10  1234    Location 2
66660000    11-APR-23 19:40:14  9999    Location 2

I'd tried to use MAX on time stamps based on some where critera and a few other little bits and bats but was not able to replicate the result I needed.

jarlh
  • 42,561
  • 8
  • 45
  • 63
hennybfd
  • 3
  • 2
  • do not post pictures of data as code. Post as text, and show your query, Better yet, set it up on sqlfiddle.com – OldProgrammer Apr 12 '23 at 01:12
  • [Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) – Ken White Apr 12 '23 at 01:27
  • Uploaded query into it and better formatted, hope that helps! My work network doesn't seem to allow me to use SQLfiddle, apologies! – hennybfd Apr 12 '23 at 01:28
  • Please show sample data and results as text. Please let us know what should happen if the most recent record for an LP is in location 1 — is that LP ignored or is it’s most recent “location 2” record what you want? – pilcrow Apr 12 '23 at 01:30
  • What if you have 2 conflicting records, i.e. records with same LP, same timestamps? Note that **unless** you have a `UNIQUE(lp, timestamp)` constraint, it does not matter for my question whether you think this may happen or not. Without a constraint, you have to assume it will. – Atmo Apr 12 '23 at 03:01
  • There shouldn't be a case of this occurring @Atmo the particular query for this should be unique location and time stamp for the LP every time it changes location. – hennybfd Apr 12 '23 at 03:04
  • Updated completely - If it's location 1 it should be ignored, or if it's earlier record is location 2 it should be ignored - only the most recent - I hope this helps @pilcrow – hennybfd Apr 12 '23 at 03:04
  • "*There shouldn't be* [a conflict]" really says nothing about your database integrity constraints (as opposed to "There is a constraint preventing that"). I have posted several versions to cope for the lack of details there. Pick the query you prefer using. – Atmo Apr 12 '23 at 03:24
  • Does this answer your question? [Oracle SQL query: Retrieve latest values per group based on time](https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time) – astentx Apr 12 '23 at 08:14

1 Answers1

0

There are multiple possibilities for what you are trying to do. Here are 2 variations of queries using a window function; they differ by how they treat ties.

With MAX:

SELECT id, timestamp, lp, location
FROM (SELECT unit_location.*, MAX(timestamp) OVER (PARTITION BY lp) AS last_timestamp
FROM unit_location
) ul
WHERE timestamp = last_timestamp

With ROW_NUMBER

SELECT id, timestamp, lp, location
FROM (SELECT unit_location.*, ROW_NUMBER() OVER (PARTITION BY lp ORDER BY timestamp DESC) AS timestamp_number
FROM unit_location
) ul
WHERE timestamp_number = 1

And here is a version with a MAX aggregate:

SELECT id, timestamp, unit_location.lp, location
FROM unit_location
JOIN (SELECT lp, MAX(timestamp) AS max_timestamp FROM unit_location GROUP BY lp) ul
    ON unit_location.lp = ul.lp AND timestamp = max_timestamp

You can choose the least expensive query and/or the query you understand best.

Atmo
  • 2,281
  • 1
  • 2
  • 21