0

I would like to apply a query to the database in such a way that it returns the last row for a given day with the same title/ticketID.

Example

ID TicketID Date Title Status
1 111 01.02.2020 Fruit is green new
2 111 01.02.2020 Fruit is green open
3 111 03.02.2020 Fruit is green open - wait
4 111 03.02.2020 Fruit is green close
5 222 04.02.2020 Fruit is yellow new
6 222 04.02.2020 Fruit is yellow open
7 222 04.02.2020 Fruit is yellow open - wait
8 222 04.02.2020 Fruit is yellow close
9 333 04.02.2020 Fruit is yellow close
10 444 05.02.2020 Fruit is blue new
11 555 05.02.2020 Fruit is red open
12 555 05.02.2020 Fruit is red open - wait
13 666 06.02.2020 Fruit is red close
14 777 06.02.2020 Fruit is pink new
15 777 07.02.2020 Fruit is pink open
16 777 08.02.2020 Fruit is pink open - wait

I would like it to return something like this when I enter a query into the database:

ID TicketID Date Title Status
2 111 01.02.2020 Fruit is green open
4 111 03.02.2020 Fruit is green close
8 222 04.02.2020 Fruit is yellow close
9 333 04.02.2020 Fruit is yellow close
10 444 05.02.2020 Fruit is blue new
12 555 05.02.2020 Fruit is red open - wait
13 666 06.02.2020 Fruit is red close
14 777 06.02.2020 Fruit is pink new
15 777 07.02.2020 Fruit is pink open
16 777 08.02.2020 Fruit is pink open - wait

So

  1. does not eliminate duplicates with a ticketID if the days are different
  2. eliminates duplicates if the ticketID is repeated on the same day
  3. takes the last ticketID for the day among the duplicates with the ticketID

What have I tried?

I tried applying with this:

SELECT 
    ID, TicketID, Date, Title, Status
FROM (
    SELECT 
        ID,
        TicketID, 
        Date, Title, 
        Status, 
        row_number() over (partition by ticketID group by date order by id desc) TEMP
    FROM
        MyTableFruit
    GROUP BY 
        Date
)
WHERE
    1 = 1
AND
    Temp = 1
ORDER BY
    Date;

Unfortunately this doesn't work, as I assumed this would take the last ticketID from duplicate ticketIDs for the day....

Any ideas on how I can resolve this?

Dominik N
  • 17
  • 4
  • Remove the `WHERE` and `GROUP BY` clauses from the inner query and give the `ROW_NUMBER ...` column an alias using `AS rn` at the end of the line and then, in the outer query, use `WHERE rn = 1` – MT0 Jun 29 '22 at 21:48
  • @MT0 I added RN before but as just the name "TEMP" I forgot to add here to the post, I corrected but it is not what I expect.... It will return me the last TicketID but will not group for a given day. – Dominik N Jun 29 '22 at 21:55
  • `SELECT ID, TicketID, "DATE", Title, Status FROM ( SELECT ID, TicketID, "DATE", Title, Status, row_number() over (partition by ticketID, TRUNC("DATE") order by id desc) AS TEMP FROM MyTableFruit ) WHERE Temp = 1 ORDER BY "DATE";` (Also, don't use `DATE` as an identifier as it is a reserved word.) – MT0 Jun 29 '22 at 22:00

0 Answers0