0

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

lewis32
  • 35
  • 1
  • 6
  • There's no join condition in your query: the query as shown will produce a cartesian product that relates *every* result row to *every* analysis row. – pmdba Apr 28 '22 at 14:31
  • What is the data type of the `date` column? I hope it's not like what you showed (**strings** including the hardcoded word "date" and some numbers). If it is of **date** data type, as it should be, it would be much more helpful if you illustrated with actual dates. If that's "too much work" for you - sorry, you are asking for free help on a volunteer based site; do your part. (And I hope the column name is not "date", which is not a valid column name...) –  Apr 28 '22 at 15:09
  • 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 29 '22 at 05:57

3 Answers3

1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I get why the rank function would work on c_date if c_date was a date or a number. But if c_date was a char (eg. "date 8" or "date 2"), how would the rank operate on the c_date column? ie. how would it know it should rank "date_8" before "date_2" (when using order by c_date desc). Would the order by be operating on the ascii representation of the numbers? – Scinana May 01 '22 at 11:23
  • In that case, you'd e.g. `order by regexp_substr(c_date, ''^[[:alpha:]]+'), to_number(regexp_substr(c_date, '\d+$'))`, i.e. sort by letters first, and then by digits which are placed to the end of the string. – Littlefoot May 01 '22 at 17:44
0

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).

  1. 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

  2. I did a GROUP BY on the temptable to get the MAX date_number value for each analyses value

  3. 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)
DharmanBot
  • 1,066
  • 2
  • 6
  • 10
Scinana
  • 402
  • 3
  • 14
0

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

meto85
  • 31
  • 3