O site Beyond Relational, tem apresentado uma série de desafios em T-SQL bastante interessantes, envolvendo problemas lógicos e dados relacionais.
O último desafio, estou traduzindo e colocando aqui no blog. Quem quiser colocar a sua implementação nos comentários, fique a vontade. Semana que vêm estarei colocando a resposta no Blog.
Vamos ao desafio: basicamente o problema proposto apresenta um roll de dados bastante simples.
C1 C2 C3
---- ---- ----
2 1 3
3 2 1
Z X Y
B C D
Y Z X
B C A
O desafio consiste em ordenar os dados horizontalmente e verticalmente em uma única query em T-SQL sem a utilização de cursores ou tabelas temporárias. Apenas o comando "select" e seus recursos.
O resultado final deve ficar assim:
C1 C2 C3
---- ---- ----
1 2 3
A B C
B C D
X Y Z
Para o problema ser considerado resolvido, as seguintes premissas devem ser obedecidas:
- Ordenar os valores horizontalmente. Organize os dados do mais baixo ao mais alto.
- Ordene os dados verticalmente. Esta é fácil, pois se trata do tradicional "order by"
- Remova valores duplicados.
- Procedures e scripts com cursores ou tabelas temporárias, não podem ser usados. Para resolver o problema use apenas o bom e velho "select". É só isso que precisa mesmo...;-)
Utilize os seguintes dados de exemplo para resolver o problema:
DECLARE @t TABLE (
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)
insert into @t (c1, c2, c3) values ('2','1','3')
insert into @t (c1, c2, c3) values ('3','2','1')
insert into @t (c1, c2, c3) values ('Z','X','Y')
insert into @t (c1, c2, c3) values ('B','C','D')
insert into @t (c1, c2, c3) values ('Y','Z','X')
insert into @t (c1, c2, c3) values ('B','C','A')
SELECT * FROM @t
Coloquem nos comentários do post, as suas implementações com seus nomes e email, que semana que vêm publico o resultado com a implementação correta e os vencedores do desafio.
Até a próxima!
Solução utilizando union e a função RANK()
ResponderExcluirDECLARE @t TABLE ( ID INT IDENTITY , c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))
insert into @t (c1, c2, c3) values ('2','1','3')
insert into @t (c1, c2, c3) values ('3','2','1')
insert into @t (c1, c2, c3) values ('Z','X','Y')
insert into @t (c1, c2, c3) values ('B','C','D')
insert into @t (c1, c2, c3) values ('Y','Z','X')
insert into @t (c1, c2, c3) values ('B','C','A')
SELECT X2.c1, X2.c2 , X2.c3
from
( SELECT MIN( CASE WHEN X1.RANK = 1 THEN X1.C ELSE NULL END ) AS c1 , MAX(CASE WHEN X1.RANK = 2 THEN X1.C ELSE NULL END) AS c2 , MAX(CASE WHEN X1.RANK = 3 THEN X1.C ELSE NULL END) AS c3
from
( SELECT ID, C , RANK() OVER (PARTITION BY ID order by C) as RANK
from
( SELECT ID,c1 FROM @t
union
SELECT ID,c2 FROM @t
union
SELECT ID,c3 FROM @t ) X ( ID, C ) ) X1 ( ID, C , RANK )
group by X1.ID ) X2 ( c1, c2, c3)
group by X2.c1, X2.c2 , X2.c3
Eugênio Spolti - Caxias do Sul - RS
A Versão sem utilizar identity
ResponderExcluirDECLARE @t TABLE ( c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))
insert into @t (c1, c2, c3) values ('2','1','3')
insert into @t (c1, c2, c3) values ('3','2','1')
insert into @t (c1, c2, c3) values ('Z','X','Y')
insert into @t (c1, c2, c3) values ('B','C','D')
insert into @t (c1, c2, c3) values ('Y','Z','X')
insert into @t (c1, c2, c3) values ('B','C','A')
SELECT X2.c1, X2.c2 , X2.c3
from
( SELECT MIN( CASE WHEN X1.RANK = 1 THEN X1.C ELSE NULL END ) AS c1 , MAX(CASE WHEN X1.RANK = 2 THEN X1.C ELSE NULL END) AS c2 , MAX(CASE WHEN X1.RANK = 3 THEN X1.C ELSE NULL END) AS c3
from
( SELECT ID, C , RANK() OVER (PARTITION BY ID order by C) as RANK
from
( SELECT ROW_NUMBER() OVER(ORDER BY c1,c2,c3 DESC) AS ID,c1 FROM @t
union
SELECT ROW_NUMBER() OVER(ORDER BY c1,c2,c3 DESC) AS ID,c2 FROM @t
union
SELECT ROW_NUMBER() OVER(ORDER BY c1,c2,c3 DESC) AS ID,c3 FROM @t ) X ( ID, C ) ) X1 ( ID, C , RANK )
group by X1.ID ) X2 ( c1, c2, c3)
group by X2.c1, X2.c2 , X2.c3
Eugênio Spolti - Caxias do Sul - RS
Eugênio, obrigado pela participação...semana que vêm divulgo os vencedores e encaminho uma das possíveis soluções.
ResponderExcluirResolução com 1 select:
ResponderExcluirDECLARE @T TABLE (
C1 CHAR(1),
C2 CHAR(1),
C3 CHAR(1)
)
INSERT INTO @t (c1, c2, c3) VALUES ('2','1','3')
INSERT INTO @t (c1, c2, c3) VALUES ('3','2','1')
INSERT INTO @t (c1, c2, c3) VALUES ('Z','X','Y')
INSERT INTO @t (c1, c2, c3) VALUES ('B','C','D')
INSERT INTO @t (c1, c2, c3) VALUES ('Y','Z','X')
INSERT INTO @t (c1, c2, c3) VALUES ('B','C','A')
SELECT CASE WHEN C1 <= C2 AND C1 < C3 THEN C1
WHEN C2 <= C1 AND C2 < C3 THEN C2
ELSE C3 END C1,
CASE WHEN C1 BETWEEN C2 AND C3 THEN C1
WHEN C1 BETWEEN C3 AND C2 THEN C1
WHEN C2 BETWEEN C1 AND C3 THEN C2
WHEN C2 BETWEEN C3 AND C2 THEN C2
ELSE C3 END C2,
CASE WHEN C1 >= C2 AND C1 > C3 THEN C1
WHEN C2 >= C1 AND C2 > C3 THEN C2
ELSE C3 END C3
FROM @T
GROUP BY CASE WHEN C1 <= C2 AND C1 < C3 THEN C1
WHEN C2 <= C1 AND C2 < C3 THEN C2
ELSE C3 END,
CASE WHEN C1 BETWEEN C2 AND C3 THEN C1
WHEN C1 BETWEEN C3 AND C2 THEN C1
WHEN C2 BETWEEN C1 AND C3 THEN C2
WHEN C2 BETWEEN C3 AND C2 THEN C2
ELSE C3 END,
CASE WHEN C1 >= C2 AND C1 > C3 THEN C1
WHEN C2 >= C1 AND C2 > C3 THEN C2
ELSE C3 END
att,
GH
Guilherme, muito obrigado pela colaboração. Minha implementação ficou próxima da sua...semana que vêm estarei publicando os resultados.
ResponderExcluirDECLARE @t TABLE ( c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))
ResponderExcluirinsert into @t (c1, c2, c3) values ('2','1','3')
insert into @t (c1, c2, c3) values ('3','2','1')
insert into @t (c1, c2, c3) values ('Z','X','Y')
insert into @t (c1, c2, c3) values ('B','C','D')
insert into @t (c1, c2, c3) values ('Y','Z','X')
insert into @t (c1, c2, c3) values ('B','C','A')
SELECT DISTINCT
C1 = CASE WHEN C1 < C2 AND C1 < C3 THEN C1
WHEN C2 < C1 AND C2 < C3 THEN C2
WHEN C3 < C1 AND C3 < C2 THEN C3 END ,
C2 = CASE WHEN (C1 > C2 AND C1 < C3) OR (C1 < C2 AND C1 > C3) THEN C1
WHEN (C2 > C1 AND C2 < C3) OR (C2 < C1 AND C2 > C3) THEN C2
WHEN (C3 > C1 AND C3 < C2) OR (C3 < C1 AND C3 > C2) THEN C3 END ,
C3 = CASE WHEN C1 > C2 AND C1 > C3 THEN C1
WHEN C2 > C1 AND C2 > C3 THEN C2
WHEN C3 > C1 AND C3 > C2 THEN C3 END
FROM @t
Leonardo...muito obrigado pela participação!...semana que vêm publicarei um post comentando sobre o problema...=)
ResponderExcluir