CODEWITHSUNDEEP
sqlsql-servert-sql
Dario Taraboi
Dario Taraboi

Reputation: 11

SQL add extra field on group by query

I have a working query to extract the minimum time of every athlete in every distance in the table "Tempi". These is a simple group by query , grouping the results by athleteID and distanceID and selecting the Minimum time. But I need now to know where and when this times are obtained. This data are in another table named "Manif". The two tables are joined by the ManifID, present in both tables. How can I retrieve the date and the city of the time? Thanks!

Working query

SELECT 
    [02_tEMPI].CodSpec, 
    [04_Spec].DescrSpecShort, 
    Min([02_Tempi].Tempo) AS MinDiTempo
FROM  
    [04_Spec] 
    INNER JOIN [03_Manif] 
    INNER JOIN [02_Tempi] 
        ON [03_Manif].IDManif = [02_Tempi].CodManif 
        ON [04_Spec].CodSpec = [02_Tempi].CodSpec
WHERE 
    [02_Tempi].CodAtleta=@CodAtleta AND 
    [03_Manif].Base = @Base 
GROUP BY 
    [04_Spec].DescrSpecShort,
    [02_Tempi].CodSpec
HAVING 
    [02_Tempi].CodSpec <'60'
ORDER BY 
    [02_Tempi].CodSpec

Tempi Table Structure

CREATE TABLE [dbo].[02_Tempi] (
    [IDRisu]            BIGINT       NOT NULL,
    [CodAtleta]         VARCHAR (12) NULL,
    [CodSpec]           VARCHAR (4)  NULL,
    [CodManif]          INT          NULL,
    [Note]              VARCHAR (14) NULL,
    [CodSocContingente] VARCHAR (14) NULL,
    [Cat]               VARCHAR (5)  NULL,
    [CatFIN]            VARCHAR (4)  NULL,
    [CatPrimato]        INT          NULL,
    [Elaborato]         SMALLINT     NULL,
    [Tempo]             VARCHAR (8)  NULL,
    CONSTRAINT [PK__02_Tempi] PRIMARY KEY CLUSTERED ([IDRisu] ASC)

mANIF TABLE STRUCTURE

CREATE TABLE [dbo].[03_Manif] (
    [IDManif]    INT           NOT NULL,
    [DescrManif] VARCHAR (100) NULL,
    [LuogoManif] VARCHAR (50)  NULL,
    [Base]       SMALLINT      NULL,
    [LinkPDF]    VARCHAR (250) NULL,
    [Data]       VARCHAR (10)  NULL,
    CONSTRAINT [PK__03_Manif] PRIMARY KEY CLUSTERED ([IDManif] ASC)
);

Thanks

Upvotes: 0

Views: 396

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

I think you can do this row_number window function:

;
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY [04_Spec].DescrSpecShort,
                                [02_Tempi].CodSpec ORDER BY [02_Tempi].Tempo ) AS rn
               FROM     [04_Spec]
                        INNER JOIN [02_Tempi] ON [04_Spec].CodSpec = [02_Tempi].CodSpec
                        INNER JOIN [03_Manif] ON [03_Manif].IDManif = [02_Tempi].CodManif
               WHERE    [02_Tempi].CodAtleta = @CodAtleta
                        AND [03_Manif].Base = @Base
                        AND [02_Tempi].CodSpec < '60'
             )
    SELECT  *
    FROM    cte
    WHERE   rn = 1

Upvotes: 2

Chris Schubert
Chris Schubert

Reputation: 1298

A potential approach

  • Select your minimum time, grouped by athleteID and distanceID
  • Cross apply that result back to the Tempi table on those 3 fields to get the ManifID from the original Tempi record (using SELECT 1 with some order if it is possible for that to have more than 1 result).

  • Using that ManifID to join with your Manif table.

    SELECT 
    Min(T1.Tempo) [Fastest Tempo],
    T1.AthleteID, 
    T1.DistanceID,
    SUBT.ManifID
FROM [02_Tempi] T1
CROSS APPLY
    (
        SELECT TOP 1 T2.ManifID
        FROM [02_Tempi] T2
        WHERE T2.AthleteID = T1.AthleteID
            AND T2.DistanceID = T1.DistanceID
        ORDER BY T2.Tempo DESC --add other differentiating columns if necessary
    ) SUBT
JOIN [03_Manif] M
    ON SUBT1.ManifID = M.ManifID
GROUP BY 
    T1.AthleteID, 
    T1.DistanceID,
    SUBT.ManifID

Upvotes: 0

Related Questions