CODEWITHSUNDEEP
sqlsql-servert-sql
LuisBerga
LuisBerga

Reputation: 67

Use alias query as a table

I'm trying to get exclusive max values from a query.

My first query (raw data) is something like that:

Material¦Fornecedor
   X         B
   X         B
   X         B
   X         C
   X         C
   Y         B
   Y         D
   Y         D

Firstly, I need to create the max values query for table above. For that, I need to count sequentially sames values of Materials AND Fornecedors. I mean, I need to count until SQL find a line that shows different material and fornecedors. After that, I'll get an result as showed below (max_line is the number of times that it found a line with same material and fornecedor):

max_line¦Material¦Fornecedor
 3          X        B
 2          X        C
 1          Y        B
 2          Y        D

In the end, I need to get the highest rows lines for an exclusive Material. The result of the query that I need to contruct, based on table above, should be like that:

max_line¦Material¦Fornecedor
 3          X        B
 2          Y        D

My code, so far, is showed below:

select * from 

(SELECT max(w2.line) as max_line, w2.Material, w2.[fornecedor]  FROM 

(SELECT w.Material, ROW_NUMBER() OVER(PARTITION BY w.Material, w.[fornecedor] 
ORDER BY w.[fornecedor] DESC) as line, w.[fornecedor]
FROM [Database].[dbo].['Table1'] w) as w2

group by w2.Material, w2.[fornecedor]) as w1

inner join (SELECT w1.Material, MAX(w1.max_line) AS maximo FROM w1 GROUP BY w1.material) as w3 
ON w1.Material = w3.Material AND w1.row = w3.maximo

I'm stuck on inner join, since I can't alias a query and use it on inner join.

Could you, please, help me?

Thank you,

Upvotes: 0

Views: 61

Answers (1)

Dale K
Dale K

Reputation: 27202

Use a window function to find the max row number then filter by it.

SELECT MAXROW, w1.Material, w1.[fornecedor]
FROM (
  SELECT w2.Material, w2.[fornecedor]
    , max([ROW]) over (partition by Material) MAXROW
  FROM (
    SELECT w.Material, w.[fornecedor]
      , ROW_NUMBER() OVER (PARTITION BY w.Material, w.[fornecedor] ORDER BY w.[fornecedor] DESC) as [ROW]
    FROM [Database].[dbo].['Table1'] w
  ) AS w2
) AS w1
WHERE w1.[ROW] = w1.MAXROW;

Upvotes: 1

Related Questions