Retrieve the last value of each data group

I’m having trouble on a query right now to retrieve only the last value saved for each group of results.

I explain myself with a table. I have these values:

| date   |  analyses | result|
| ------ | --------- | ----- |
| date 8 |analysis A |  10   |
| date 7 |analysis A |  15   |
| date 6 |analysis C |  12   |
| date 5 |analysis A |  13   |
| date 4 |analysis B |  17   |
| date 3 |analysis A |  25   |
| date 2 |analysis B |  20   |
| date 1 |analysis C |  10   |

I retrieve in my query all the results obtained for 3 types of analyses and I display their creation date from the most recent to the oldest.

I would now like to improve my query if possible so that it only gives me the last values for each analysis.

The expected result is:

| date   | analyses  | result|
| -----  | --------  | ----- |
| date 8 |analysis A |  10   |
| date 6 |analysis C |  12   |
| date 4 |analysis B |  17   |

My request now looks like this:

SELECT
    date,
    analyses,
    results
FROM
    table_results,
    table_analyses,
WHERE
    analyse in ('analysis A','analysis B','analysis C')
ORDER BY date DESC

I tried to use GROUP BY on the attribute analyses but without success (error: is not a GROUP BY expression...).

If anyone has an idea thank you in advance,

Have a good day

CodePudding user response：

To me, it looks as this:

Sample data (didn't feel like typing too much; read what mathguy commented. Whatever C_DATE is (should be DATE datatype), query that follows will still work):

SQL> select * from test;

    C_DATE ANALYSES       RESULT
---------- ---------- ----------
         8 a                  10
         7 a                  15
         6 c                  12
         5 a                  13
         4 b                  17
         3 a                  25
         2 b                  20
         1 c                  10

8 rows selected.

First rank rows per each analyses by c_date in descending order (so that the "last" date ranks as the highest), and then fetch these rows:

SQL> with temp as
  2    (select c_date, analyses, result,
  3       rank() over (partition by analyses order by c_date desc) rnk
  4     from test
  5    )
  6  select c_date, analyses, result
  7  from temp
  8  where rnk = 1
  9  order by analyses;

    C_DATE ANALYSES       RESULT
---------- ---------- ----------
         8 a                  10
         4 b                  17
         6 c                  12

SQL>

CodePudding user response：

Create Table:

CREATE TABLE TEST_TABLE(ADATE VARCHAR(20), ANALYSES VARCHAR(20), RESULT VARCHAR(20))

INSERT INTO TEST_TABLE VALUES('date 8', 'analysis A', '10');
INSERT INTO TEST_TABLE VALUES('date 7', 'analysis A', '15');
INSERT INTO TEST_TABLE VALUES('date 6', 'analysis C', '12');
INSERT INTO TEST_TABLE VALUES('date 5', 'analysis A', '13');
INSERT INTO TEST_TABLE VALUES('date 4', 'analysis B', '17');
INSERT INTO TEST_TABLE VALUES('date 3', 'analysis A', '25');
INSERT INTO TEST_TABLE VALUES('date 2', 'analysis B', '20');
INSERT INTO TEST_TABLE VALUES('date 1', 'analysis C', '10');

SELECT * FROM TEST_TABLE: https://i.stack.imgur.com/PnzCm.png

Solution:

SELECT
    MAX(ADATE) KEEP(DENSE_RANK LAST ORDER BY ADATE) AS ADATE
    , ANALYSES
    , MAX(RESULT) KEEP(DENSE_RANK LAST ORDER BY ADATE) AS RESULT
FROM
    TEST_TABLE
GROUP BY
    ANALYSES
ORDER BY
    ADATE DESC

Result: https://i.stack.imgur.com/DpsDO.png

CodePudding user response：

You can still get the result you want using a GROUP BY but you would have to break your attempt into 2 levels.

Assuming you already have a query, table or view that gets you the following - call it "test", so if I call SELECT * FROM test, I get the below:

| date   |  analyses | result|
| ------ | --------- | ----- |
| date 8 |analysis A |  10   |
| date 7 |analysis A |  15   |
| date 6 |analysis C |  12   |
| date 5 |analysis A |  13   |
| date 4 |analysis B |  17   |
| date 3 |analysis A |  25   |
| date 2 |analysis B |  20   |
| date 1 |analysis C |  10   |

You haven't given us data types here, so I have assumed your date column is a string (if it's a different data type you can still cast this to an integer or number type).

I defined a temptable where I extracted the number part of your date (instead of "date 8" I would just use 8) and casted it to be a number. This was saved as the "date_number" column and that way you can run group by operations like MAX on it
I did a GROUP BY on the temptable to get the MAX date_number value for each analyses value
I called all the values in the original table (select * from test), with a WHERE clause, telling it that I wanted all the rows where my date was equal to the values in part (2) - so basically all the rows where there was a maximum date for each analyses type

In short the code looks like this

with  temptable as
        (select to_number(substr(to_char(the_date),6,1)) as date_number, 
                analyses, 
                result
        from test)
select * 
from test
where 
    to_number(substr(to_char(the_date),6,1)) in 
                   (select max(date_number) from temptable
                    group by analyses)