Tutorial :How to output only one max value from this query in SQL?



Question:

Yesterday Thomas helped me a lot by providing exactly the query I wanted. And now I need a variant of it, and hopes someone can help me out.

I want it to output only one row, namely a max value - but it has to build on the algorithm in the following query:

WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date                                            UNION ALL                                            SELECT     DATEADD(d, 1, Date) AS Expr1                                            FROM         Calendar AS Calendar_1                                            WHERE     (DATEADD(d, 1, Date) < @EndDate))      SELECT     C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]       FROM         Calendar AS C CROSS JOIN                              Country AS C2 LEFT OUTER JOIN                              Requests AS R ON C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID       WHERE     (C2.Country = @Country)       GROUP BY C.Date, C2.Country OPTION (MAXRECURSION 0)  

The output from above will be like:

Date            Country         Allocated testers  06/01/2010      Chile             3  06/02/2010      Chile             4  06/03/2010      Chile             0  06/04/2010      Chile             0  06/05/2010      Chile            19  

but what I need right now is

Allocated testers             19  

that is - only one column - one row - the max value itself... (for the (via parameters (that already exists)) selected period of dates and country)


Solution:1

WITH  Calendar          AS (               SELECT                CAST(@StartDate AS datetime) AS Date               UNION ALL               SELECT                DATEADD(d, 1, Date) AS Expr1               FROM                Calendar AS Calendar_1               WHERE                ( DATEADD(d, 1, Date) < @EndDate )             )  SELECT TOP 1 *  FROM   (               SELECT      C.Date     ,C2.Country     ,COALESCE(SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]    FROM      Calendar AS C      CROSS JOIN Country AS C2      LEFT OUTER JOIN Requests AS R        ON C.Date BETWEEN R.[Start date] AND R.[End date]           AND R.CountryID = C2.CountryID    WHERE      ( C2.Country = @Country )    GROUP BY      C.Date     ,C2.Country  OPTION      ( MAXRECURSION 0 )      ) lst      ORDER BY lst.[Allocated testers] DESC  


Solution:2

use order and limit

ORDER BY 'people needed DESC' LIMIT 1   

EDITED

as LIMIT is not exist in sql

use ORDER BY and TOP

select TOP 1 .... ORDER BY 'people needed' DESC  


Solution:3

Full example following the discussion in @Salil answer..

WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date                    UNION ALL                    SELECT     DATEADD(d, 1, Date) AS Expr1                    FROM         Calendar AS Calendar_1                     WHERE     (DATEADD(d, 1, Date) < @EndDate))  SELECT  TOP 1   C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]  FROM    Calendar AS C CROSS JOIN          Country AS C2 LEFT OUTER JOIN          Requests AS R ON C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID  WHERE     (C2.Country = @Country)  GROUP BY C.Date, C2.Country   ORDER BY 3 DESC  OPTION (MAXRECURSION 0)  

the ORDER BY 3 means order by the 3rd field in the SELECT statement.. so if you remove the first two fields, change this accordingly..


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »