phil phil - 3 months ago 6
SQL Question

Oracle query - Last Date multiple Items

I have some troubles with a query. The query is made for getting Article Nr. and Modification Date. Afterwards we generate a Report. It worked very well until now.

For every Product there will be 4 Stages with four Dates.
Unfortunately in the DB sometimes more than 4 Stages. In some cases the Product pass the Process twice. this means 8 Stages with 8 dates.
what i try to get:


  • a list with some products

  • the newest stages (youngest Date)

  • Ignore, if exits the oldest Production Steps



this is my Code, (i create pseudo Code)


I tried to mix the Artikel number with the date (
concat()
).
To get an unique String.

But it is not working very well (high processing time).

SELECT
*
FROM
(
SELECT
/* Some Calculations & Cases and Critera */
FROM
(
SELECT
/* Get the Product Columns */
ArtNumber,
modDate,
weight
FROM
/* four different Tables */
WHERE
/* Criterialist to get the Products */
modDate>sysdate -360
/* what i think, that can solve the Problem */
AND CONCAT(ArtNumber, modDate) IN
(
SELECT DISTINCT
ArtNumber
FROM
WHERE
(
stateentrytime >sysdate - 13
AND stateentrytime<sysdate - 5
)
(
SELECT
MAX(modTime) AS highestDate,
ArtNumber,
CONCAT (ArtNumber, modTime) AS ArtMod
FROM
WHERE
/* Criterialist to get the Products */
AND ArtNumber IN
(
SELECT
ArtNumber
FROM
WHERE
modTime>sysdate -360
GROUP BY
h.ArtNumber
)
GROUP BY
h.ArtNumber,
CONCAT (ArtNumber, modTime)
)
)
)
ftd ,
WHERE
/* Criterialist to get the Products */
)
WHERE
/* more Criteria */


Do you have some hints for me?
for


  1. getting the modDate

  2. optimize the query





What i expect


ArtNumber | modTime

------ | ------

12345 | 23.08.2016

12346 | 20.07.2016

12347 | 23.05.2016

12348 | 23.03.2016



  • one Date for one Item



What i get


ArtNumber | modTime

------ | ------

12345 | 23.08.2016

12346 | 20.07.2016

12345 | 23.05.2016

12346 | 23.03.2016



  • same Item Number

  • with different Date



The Problem is, when i try to get the MAX. I get only the Item with the hightest Date back.

many thanks Phil

Answer

You want to aggregate data. You are looking for the MAX(modtime) per artnumber. This translates to MAX(modtime) ... GROUP BY artnumber. (Without the GROUP BY you'd get just one row aggregating all recods.)

Hence:

select artnumber, max(modtime) as modtime
from ...
where ...
group by artnumber
order by artnumber;
Comments