Use alias query as a table

Question

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,

You need to explain further what you are trying to achieve, because as it stands you are just duplicating data that is already available in your query - you're not joining anything new on. — Dale K
– Dale K, Commented Aug 26, 2020 at 3:17
Does row, as shown in your data, exist in your table? Or is that the computed row number? — Dale K
– Dale K, Commented Aug 26, 2020 at 3:42

Dale K · Accepted Answer · 2020-08-26 04:19:58Z

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;

Thank you, Dale! Off course, you're right. I'll pay attention on next questions. Appreciated.

Collectives™ on Stack Overflow

Use alias query as a table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related