SQL - How do I use a subquery instead of a Temp Table to make this query run?-CodePudding

I have this SQL query that I running thorough the Power BI/Snowflake connector:

create temporary table DS_TEMP (INC text, Ass_Group text, SEQ_NO int);

INSERT INTO DS_TEMP
SELECT INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER, Assigned_Group, Assigned_Group_SEQ_NO from INCIDENT_HISTORY_PUBLIC where ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$';

SELECT DISTINCT INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER, ASSIGNED_GROUP, ASSIGNED_GROUP_SEQ_NO, History_Start_Date
from INCIDENT_HISTORY_PUBLIC, DS_TEMP
where INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER = DS_TEMP.INC AND INCIDENT_HISTORY_PUBLIC.ASSIGNED_GROUP NOT LIKE '%Help_Desk%' AND ASSIGNED_GROUP_SEQ_NO < DS_TEMP.SEQ_NO
ORDER BY INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER, ASSIGNED_GROUP_SEQ_NO;

Instead of creating a temp table, is there a way I just use a sub-query to achieve the same results?

I've tried to use an inner join along with max() but am having no luck.

CodePudding user response：

Yes, you can use a subquery to achieve the same results as the temporary table in your SQL query. Here is an example of how you can rewrite your query using a subquery:

    SELECT DISTINCT INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER,
                ASSIGNED_GROUP,
                ASSIGNED_GROUP_SEQ_NO,
                History_Start_Date
FROM INCIDENT_HISTORY_PUBLIC
INNER JOIN (
  SELECT INCIDENT_NUMBER as INC, MAX(ASSIGNED_GROUP_SEQ_NO) as SEQ_NO
  FROM INCIDENT_HISTORY_PUBLIC
  WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
  GROUP BY INCIDENT_NUMBER
) DS_TEMP
ON INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER = DS_TEMP.INC
AND ASSIGNED_GROUP_SEQ_NO < DS_TEMP.SEQ_NO
AND INCIDENT_HISTORY_PUBLIC.ASSIGNED_GROUP NOT LIKE '%Help_Desk%'
ORDER BY INCIDENT_HISTORY_PUBLIC.INCIDENT_NUMBER, ASSIGNED_GROUP_SEQ_NO;

In this query, we use a subquery in the FROM clause to find the maximum value of the ASSIGNED_GROUP_SEQ_NO column for each INCIDENT_NUMBER where the ASSIGNED_GROUP column starts with ' DS_'. We then use an INNER JOIN to join the results of this subquery with the INCIDENT_HISTORY_PUBLIC table on the INCIDENT_NUMBER column. The rest of the query is similar to the original query.

Note that we use the MAX() function in the subquery to find the maximum value of the ASSIGNED_GROUP_SEQ_NO column for each INCIDENT_NUMBER. This will ensure that we only select records from the INCIDENT_HISTORY_PUBLIC table that have a value of ASSIGNED_GROUP_SEQ_NO that is less than the maximum value of this column for that INCIDENT_NUMBER.

By using a subquery instead of a temporary table, we can simplify our query and avoid creating an additional table in the database.