Toad World® Forums

Need help with SQL Query


#1

Hello All,

I am new to SQL database queries and am looking for help on how to execute this one.

I think I need to use a subquery, not really sure, so I will try to break it down in steps. What I would like to do (in one query) is…

Step 1: return all the rows in the table where ERROR_CODE=3

Step 2: take all the SERIAL_NUMBERs from Step 1 and query the table again for all rows containing those SERIAL_NUMBERs

Step 3: group the results of Step 2 by SERIAL_NUMBER

Step 4: order the rows within those groups by TIME_STAMP in descending order

Step 5: order those groups by the maximum TIME_STAMP value in each group in descending order

Thanks in advance,

Aaron


#2

Perhaps like this:

WITH groupBySerialNumber

AS ( SELECT serial_number, MAX(time_stamp) AS GroupMaxTimeStamp

FROM tableName

WHERE ERROR_CODE = 3

GROUP BY serial_number)

SELECT t.*

FROM groupBySerialNumber g, tableName t

WHERE t.serial_number = g.serial_number AND ERROR_CODE = 3

ORDER BY GroupMaxTimeStamp DESC, TIME_STAMP DESC;

Br

Fredrik


#3

Thank you so much, it worked perfectly. I only had to make one alteration based on a guess, to remove the second “AND ERROR_CODE=3”.

How would you explain this query in plain English?

Best,

Aaron


#4

A group by subquery to get the max time_stamp for all groups by serial_number joined with the original table.

You can write it this way, too, without WITH clause:

SELECT t.*

FROM ( SELECT serial_number, MAX (time_stamp) AS GroupMaxTimeStamp

FROM tableName

WHERE ERROR_CODE = 3

GROUP BY serial_number) g,

tableName t

WHERE t.serial_number = g.serial_number AND ERROR_CODE = 3

ORDER BY GroupMaxTimeStamp DESC, TIME_STAMP DESC;

You selecting the table twice, once for grouping and one to join with the grouping.

If its a very large table and not so many rows with error_code=3 then perhaps it is more efficient to filter the error_code first and then do the rest:

WITH tableWithErrorCode3

AS ( SELECT serial_number, time_stamp

FROM tableName

WHERE ERROR_CODE = 3)

SELECT t.*

FROM ( SELECT serial_number, MAX (time_stamp) AS GroupMaxTimeStamp

FROM tableWithErrorCode3

GROUP BY serial_number) g,

tableWithErrorCode3 t

WHERE t.serial_number = g.serial_number

ORDER BY GroupMaxTimeStamp DESC, TIME_STAMP DESC;