I have a table named Ticket Numbers, which (for this example) contain the columns:

Ticket_Number
Assigned_Group
Assigned_Group_Sequence_No
Reported_Date

Each ticket number could contain 4 rows, depending on how many times the ticket changed assigned groups. Some of these rows could contain an assigned group of "Desktop Support," but some may not. Here is an example:

Example of raw data

What I am trying to accomplish is to get the an output that contains any ticket numbers that contain 'Desktop Support', but also the assigned group of the max sequence number. Here is what I am trying to accomplish with SQL:

Queried Data

I'm trying to use SQL with the following query but have no clue what I'm doing wrong:

select ih.incident_number,ih.assigned_group, incident_history2.maxseq, incident_history2.assigned_group
from incident_history_public as ih
left join
(
  select max(assigned_group_seq_no) maxseq, incident_number, assigned_group
  from incident_history_public
  group by incident_number, assigned_group
) incident_history2
  on ih.incident_number = incident_history2.incident_number
  and ih.assigned_group_seq_no = incident_history2.maxseq
  where ih.ASSIGNED_GROUP LIKE '%DS%'

Does anyone know what I am doing wrong?

CodePudding user response：

You might want to create a proper alias for incident_history. e.g.

from incident_history as incident_history1

and

on incident_history1.ticket_number = incident_history2.ticket_number
  and incident_history1.assigned_group_seq_no = incident_history2.maxseq

CodePudding user response：

In my humble opinion a first error could be that I don't see any column named "incident_history2.assigned_group". I would try to use common table expression, to get only ticket number that contains "Desktop_support":

WITH desktop as (
   SELECT distinct Ticket_Number
   FROM incident_history
   WHERE Assigned_Group = "Desktop Support"
),

Than an Inner Join of the result with your inner table to get ticket number and maxSeq, so in a second moment you can get also the "MAXGroup":

WITH tmp AS (
SELECT i2.Ticket_Number, i2.maxseq
FROM desktop D inner join 
    (SELECT Ticket_number, max(assigned_group_seq_no) as maxseq
     FROM incident_history
     GROUP BY ticket_number) as i2
     ON D.Ticket_Number = i2.Ticket_Number
)

SELECT i.Ticket_Number, i.Assigned_Group as MAX_Group, T.maxseq, i.Reported_Date
FROM tmp T inner join incident_history i
     ON T.Ticket_Number = i.Ticket_Number and i.assigned_group_seq_no = T.maxseq

I think there are several different method to resolve this question, but I really hope it's helpful for you!

For more information about Common Table Expression: https://www.essentialsql.com/introduction-common-table-expressions-ctes/