CODEWITHSUNDEEP
sqldateselectmax
Thiago Braga
Thiago Braga

Reputation: 129

Select max data with 3 tables

I can't get the right results. I have 3 tables:

table: Aluno
id_aluno    nome
1           Bruno
2           Carlos

table: Serie
id_serie    id_aluno    descricao
1           1           Tipo A
2           1           Tipo B
3           2           Tipo A

table: Treino
id_treino   id_serie    data
1           1           2015-12-10
2           2           2015-12-12
3           3           2015-12-10

I want the following results:

nome     descricao    data
Bruno    TIPO B       2015-12-12
Carlos   TIPO A       2015-12-10

The problem is that the GROUP BY clause should have column "id_aluno" but it's not foreign key of the table which has the date. There is a intermediate table between them. I don't know how to do. I hope you can help me.

Upvotes: 4

Views: 1073

Answers (4)

MAK
MAK

Reputation: 7270

Here I have did it using the multiple Common table expressions.

Script:

WITH CTE
AS
(
    SELECT TOP 2 id_Treino,id_serie,data
    FROM Treino
    ORDER BY DATA DESC
), 
cte2 
as
(
    SELECT a.nome,s.descricao,c.data
    FROM aluno AS a
    INNER JOIN Serie AS s
    ON a.id_aluno = s.id_aluno
    INNER JOIN cte AS c
    ON s.id_serie = c.id_serie
)
SELECT * FROM cte2;

Output:

nome        descricao       DATA
------------------------------
Bruno       Tipo B      2015-12-12
Carlos      Tipo A      2015-12-10

Upvotes: 0

Y.B.
Y.B.

Reputation: 3596

You can use Group By on any column of the From tables including those linked through Join. You can use Join to link as many tables as you need.

Select Aluno.nome, Serie.descricao, Max(Treino.data) As data
From Aluno
Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
Inner Join Treino On Treino.id_serie = Serie.id_serie
Group By Aluno.nome, Serie.descricao

OK, with the comment in mind for MS SQL it can be written like that:

Select nome, descricao, data
From (
    Select Aluno.nome, Serie.descricao, Treino.data,
        RANK() OVER (Partition By Aluno.nome Order By Treino.data Desc) AS Ordinal
    From Aluno
    Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
    Inner Join Treino On Treino.id_serie = Serie.id_serie
) Ranked
Where Ordinal = 1

By the way, what should happen if you have another record in Treino: 4, 1, 2015-12-12 or 5, 2, 2015-12-12 for that matter? You need a way to prioritize Serie too. Say, like that:

Select nome, descricao, data
From (
    Select Aluno.nome, Serie.descricao, Treino.data,
        ROW_NUMBER() OVER (Partition By Aluno.nome Order By Treino.data Desc, Serie.descricao) AS Ordinal
    From Aluno
    Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
    Inner Join Treino On Treino.id_serie = Serie.id_serie
) Ranked
Where Ordinal = 1

Upvotes: 0

Othman Dahbi-Skali
Othman Dahbi-Skali

Reputation: 632

Use this query

Select a.nome, s.descricao, t.data
FROM Aluno a
join Serie s on (s.id_aluno = a.id_aluno)
join Treino t on (t.id_serie = s.id_serie)

Upvotes: 0

morgb
morgb

Reputation: 2312

You can join the tables based on the existing keys, but you would then need to specify that you want the max date based only on the person, something like this:

SELECT
    a.nome,
    s.descricao,
    t.data
FROM Aluno a
    JOIN Serie s
        ON s.id_aluno = a.id_aluno
    JOIN Treino t
        ON t.id_serie = s.id_serie
WHERE t.data = ( --get max date by person, excluding serie
                SELECT MAX(t1.data)
                FROM Aluno a1
                JOIN Serie s1
                    ON s1.id_aluno = a1.id_aluno
                JOIN Treino t1
                    ON t1.id_serie = s1.id_serie
                WHERE s1.id_aluno = s.id_aluno 
               )

Upvotes: 1

Related Questions