Fórum Select usando MAX() em duas colunas e retornar a ID as linhas. #546504

SELECT DISTINCT
	TB02021_CODIGO,
	MAX(TB02021_CONTPB) OVER (PARTITION BY TB02021_CODIGO) as CONTPB,
	MAX(TB02021_CONTCOLOR) OVER (PARTITION BY TB02021_CODIGO ORDER BY TB02021_CODIGO DESC) AS CONTCOLOR
	--TB02021_CODIGO AS Q, 
	--TB02021_CODIGO AS T
FROM TB02021 WHERE TB02021_NUMSERIE = 'QVQ3810616' 
group by TB02021_CODIGO

SELECT
  tb02021.*
FROM
  (SELECT TB02021_CODIGO, MAX(TB02021_CONTPB) AS max_CONTPB FROM TB02021 GROUP BY TB02021_CODIGO) AS CONTPB
INNER JOIN
  (SELECT TB02021_CODIGO, TB02021_CONTPB, MAX(TB02021_CONTCOLOR) AS max_CONTCOLOR FROM TB02021 GROUP BY TB02021_CODIGO, TB02021_CONTPB) AS CONTCOLOR
    ON  CONTCOLOR.TB02021_CODIGO = CONTPB.TB02021_CODIGO
    AND CONTCOLOR.TB02021_CODIGO = CONTPB.max_CONTPB
INNER JOIN
  TB02021
    ON  TB02021.TB02021_CODIGO = CONTCOLOR.TB02021_CODIGO
    AND TB02021.TB02021_CONTPB = CONTCOLOR.TB02021_CONTPB
    AND TB02021.TB02021_CONTCOLOR  = CONTCOLOR.max_CONTCOLOR
   

select max(tab.contador_colunaA) as maxA, cont1.ID as idA,
       max(tab.contador_colunaB) as maxB, cont2.ID as idB
from tabela tabela_principal tab inner join tabela_contador cont1 on (tab.contador_colunaA = cont1.ID)
                                 inner join tabela_contador cont2 on (tab.contador_colunaB = cont2.ID)
group by cont1.ID, cont2.ID

select 
	max(tab.TB02021_CONTPB) 
		as maxA, 
	cont1.TB02021_CODIGO 
		as idA,
	max(tab.TB02021_CONTCOLOR)
		as maxB, 
	cont2.TB02021_CODIGO 
		as idB
from 
	TB02021 tab 
		inner join 
			TB02021 cont1 
				on 
					(tab.TB02021_CODIGO = cont1.TB02021_CODIGO)
		        inner join 
			TB02021 cont2
		on 
	(tab.TB02021_CODIGO = cont2.TB02021_CODIGO)
WHERE 
	tab.TB02021_NUMSERIE = '79G1TZ3'
group by 
	cont1.TB02021_CODIGO, cont2.TB02021_CODIGO

from  TB02021 tab  inner join  TB02021 cont1 on...
                   inner join  TB02021 cont2 on...

declare @max_PB, @max_COLOR integer

set @max_PB    = (select max(tab.TB02021_CONTPB) from TB02021)
set @max_COLOR = (select max(tab.TB02021_CONTCOLOR) from TB02021)

select ID, @max_PB as Max from TB02021 where CONTPB = @max_PB
UNION ALL
select ID, @max_PCOLOR as Max from TB02021 where CONTCOLOR = @max_COLOR

SELECT
(select ID FROM TABELA ORDER BY CONTADOR_A DESC LIMIT 1) AS ID_A,
(select CONTADOR_A FROM TABELA ORDER BY CONTADOR_A DESC LIMIT 1) AS CONTADOR_A,
(select ID FROM TABELA ORDER BY CONTADOR_B DESC LIMIT 1) AS ID_B,
(select CONTADOR_B FROM TABELA ORDER BY CONTADOR_B DESC LIMIT 1) AS CONTADOR_B
FROM TABELA