Can some expert please explain what this query is doing?

Question

This is the query in one of the Reports that I am trying to fix. What is being done here?

select
   * 
from
   (
      SELECT
         [dbo].[RegistrationHistory].[AccountNumber],
         [dbo].[RegistrationHistory].[LinkedBP],
         [dbo].[RegistrationHistory].[SerialNumber],
         [dbo].[RegistrationHistory].[StockCode],
         [dbo].[RegistrationHistory].[RegistrationDate],
         [dbo].[RegistrationHistory].[CoverExpiry],
         [dbo].[RegistrationHistory].[LoggedDate] as 'CoverExpiryNew',
         ROW_NUMBER() OVER(PARTITION BY [dbo].[RegistrationHistory].[SerialNumber] 
      ORDER BY
         LoggedDate asc) AS seq,
         [dbo].[Registration].[StockCode] as 'CurrentStockCode' 
      FROM
         [SID_Repl].[dbo].[RegistrationHistory] 
         LEFT JOIN
            [SID_Repl].[dbo].[Registration] 
            on [dbo].[RegistrationHistory].[SerialNumber] = [dbo].[Registration].[SerialNumber] 
      where
         [dbo].[RegistrationHistory].[StockCode] in 
         (
            'E4272HL1',
            'E4272HL2',
            'E4272HL3',
            'E4272H3',
            'OP45200HA',
            'OP45200HM',
            'EOP45200HA',
            'EOP45200HM',
            '4272HL1',
            '4272HL2',
            '4272HL3',
            '4272H3'
         )
   )
   as t 
where
   t.seq = 1 
   and CurrentStockCode in 
   (
      'E4272HL1',
      'E4272HL2',
      'E4272HL3',
      'E4272H3',
      'OP45200HA',
      'OP45200HM',
      'EOP45200HA',
      'EOP45200HM',
      '4272HL1',
      '4272HL2',
      '4272HL3',
      '4272H3'
   )

I am looking for a simplified way of splitting this query into step by step, so that I can see where it is going wrong.

It might help if you explained what is wrong? note the `order by` clause is redundant as is the outer `where CurrentStockCode` — Stu, Aug 15 '22 at 15:06
How would anyone here know? We know nothing about the table structure, what the data means, what the query is trying to do, what the expected results are. — OldProgrammer, Aug 15 '22 at 15:09
If the ORDER BY clause that goes with the ROW_NUMBER function were on the same line (or otherwise formatted), it would be more obvious that row numbers are generated in ascending order of LoggedDate for each value of SerialNumber. The alias assigned to that column is not helpful - it just a sequence value. — SMor, Aug 15 '22 at 15:12
That `LEFT JOIN` might as well be an `INNER JOIN` due to the `WHERE`; it is **impossible** for `RegistrationHistory.StockCode` to have a value in the `IN` if no row was found in the table `RegistrationHistory`. Also, [3+ part naming on Columns will be Deprecated](//wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) and should be avoided. You are far better off aliasing your objects in the `FROM` and using those to qualify your columns. This avoids the use of a feature that will be deprecated and possibly removed in a future version, and makes your code much more succinct and readable. — Thom A, Aug 15 '22 at 15:17
I'm also not sure why you then check the value of `CurrentStockCode` in the outer query, as you already did in the inner query (`CurrentStockCode` is the alias of `[Registration].[StockCode]`). I suggest against the use single quotes (`'`) for aliases too. Single quotes are for literal strings, not delimit identifying object names. Some versions of the syntax are deprecated, and their behaviour can result in some "gotchas" for people that thing that they can use a single quote alias anywhere. — Thom A, Aug 15 '22 at 15:19
[Order Of Execution of the SQL query](https://stackoverflow.com/a/4596739/3404097) — philipxy, Aug 15 '22 at 18:20

score 1 · Accepted Answer · answered Aug 15 '22 at 15:09

ROW_NUMBER in a subquery combined with a filter on it in the outer query is an idiom to filter out all but the first row in a group. So here

ROW_NUMBER() OVER(PARTITION BY [dbo].[RegistrationHistory].[SerialNumber])

Assigns the row with the lowest SerialNumber 1, the next lowest, 2, etc. Then later

where
   t.seq = 1

removes all but the row with the lowest serial number from the result.

Can some expert please explain what this query is doing?

1 Answers1