CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ryan Smith
Ryan Smith

Reputation: 87

Multiple MAX values in a single query

I'm taking this data and inserting it into another table as part of a conversion process. Unfortunately I'm not able to modify the table schema in either my source or target location.

Schema is as follows (super simplified but the data types can't be modified, just obviously cast)

CREATE TABLE [dbo].[TestScores](
    [id] [INT] NOT NULL,
    [EXAMNE_ID] [VARCHAR](40) NULL,
    [TestName] VARCHAR(30) NULL,
    [PASS_STA] [VARCHAR](5) NULL,
    [TST_DTE] [VARCHAR](8) NULL,
    [STD_SCOR] [VARCHAR](3) NULL,
) ON [PRIMARY]

I have the following source data: (Obviously just a snapshot of the data but for a single person)

+----+-----------+-----------------+----------+----------+----------+
| id | EXAMNE_ID |    TestName     | PASS_STA | TST_DTE  | STD_SCOR |
+----+-----------+-----------------+----------+----------+----------+
|  1 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
|  2 |     00001 | Science         | Fail     | 20160608 |        8 |
|  3 |     00001 | Reading         | Fail     | 20160608 |        2 |
|  4 |     00001 | Math            | Fail     | 20160608 |        8 |
|  5 |     00001 | Writing         | Fail     | 20160608 |        7 |
|  6 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
|  7 |     00001 | Science         | Fail     | 20160608 |        8 |
|  8 |     00001 | Reading         | Fail     | 20160608 |        2 |
|  9 |     00001 | Math            | Fail     | 20160608 |        8 |
| 10 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 11 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 12 |     00001 | Science         | Fail     | 20160608 |        8 |
| 13 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 14 |     00001 | Math            | Fail     | 20160608 |        8 |
| 15 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 16 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 17 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 18 |     00001 | Science         | Fail     | 20160608 |        8 |
| 19 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 20 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 21 |     00001 | Math            | Fail     | 20160608 |        8 |
| 22 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 23 |     00001 | Writing         | Fail     | 20160930 |       10 |
| 24 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 25 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 26 |     00001 | Science         | Fail     | 20160608 |        8 |
| 27 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 28 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 29 |     00001 | Math            | Fail     | 20160608 |        8 |
| 30 |     00001 | Writing         | Fail     | 20160608 |        7 |
| 31 |     00001 | Writing         | Fail     | 20160930 |       10 |
| 32 |     00001 | Social Studies  | Fail     | 20160608 |        7 |
| 33 |     00001 | Social Studies  | Fail     | 20160930 |       10 |
| 34 |     00001 | Science         | Fail     | 20160608 |        8 |
| 35 |     00001 | Reading         | Fail     | 20160608 |        2 |
| 36 |     00001 | Reading         | Fail     | 20160930 |        5 |
| 37 |     00001 | Math            | Fail     | 20160608 |        8 |
| 38 |     00001 | Writing         | Pass     | 20160608 |        7 |
| 39 |     00001 | Writing         | Pass     | 20160930 |       10 |
| 40 |     00001 | Social Studies  | Pass     | 20160608 |        7 |
| 41 |     00001 | Social Studies  | Pass     | 20160930 |       10 |
| 42 |     00001 | Science         | Pass     | 20160608 |        8 |
| 43 |     00001 | Reading         | Pass     | 20160608 |        2 |
| 44 |     00001 | Reading         | Pass     | 20160930 |        5 |
| 45 |     00001 | Reading         | Pass     | 20161202 |        9 |
| 46 |     00001 | Math            | Pass     | 20160608 |        8 |
+----+-----------+-----------------+----------+----------+----------+

My end result target data should look like this:

+----+-----------+-----------------+----------+----------+----------+
| id | EXAMNE_ID |    TestName     | PASS_STA | TST_DTE  | STD_SCOR |
+----+-----------+-----------------+----------+----------+----------+
| 39 |     00001 | Writing         | Pass     | 20160930 |       10 |
| 41 |     00001 | Social Studies  | Pass     | 20160930 |       10 |
| 42 |     00001 | Science         | Pass     | 20160608 |        8 |  
| 45 |     00001 | Reading         | Pass     | 20161202 |        9 |
| 46 |     00001 | Math            | Pass     | 20160608 |        8 |
+----+-----------+-----------------+----------+----------+----------+

I know I gave a ton of extra data since I'm really only looking for results that have a PASS_STA of 'Pass', but I wanted to give more of a complete picture.

Essentially the logic that occurs is that for a given person, when the exam is passed, pull out the greatest value of the TST_DTE and STD_SCOR columns. I'm trying this with what seems like multiple variants of max but it's heading in the wrong direction.

Currently this query isn't giving me what I'm looking for.

SELECT DISTINCT 
EXAMNE_ID, PASS_STA, TestName, MAX(TST_DTE) AS 'TST_DTE', MAX(STD_SCOR) AS 'STD_SCOR' 
FROM dbo.TestScores  
GROUP BY EXAMNE_ID, PASS_STA, TestName, STD_SCOR 
HAVING PASS_STA = 'Pass'

Part of me is thinking I need to nest the query since I'm looking for two max values (plus one is a date - well, a date-ish, since it's in a varchar) and the other is a number (but again, stored in a varchar) or maybe somehow the RANK functionality?

I'd rather not have to do aggregate CASE statements for each of the five subject areas. There are actually a few dozen subject areas total, it's just this one test type that I'm showing here has five. So while I think aggregate case statements may work, it may be too cumbersome.

Any ideas?

Thanks in advance

Upvotes: 2

Views: 75

Answers (3)

user7715598
user7715598

Reputation: 

;With cte(id , EXAMNE_ID ,    TestName     , PASS_STA , TST_DTE  , STD_SCOR )
AS
(
SELECT  1 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT  2 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT  3 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT  4 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT  5 , '00001' , 'Writing'         , 'Fail'     , '20160608',  7   Union all
SELECT  6 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT  7 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT  8 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT  9 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT 10 , '00001' , 'Writing'         , 'Fail'     , '20160608',  7   Union all
SELECT 11 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT 12 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT 13 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT 14 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT 15 , '00001' , 'Writing'         , 'Fail'     , '20160608',  7   Union all
SELECT 16 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT 17 , '00001' , 'Social Studies'  , 'Fail'     , '20160930', 10   Union all
SELECT 18 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT 19 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT 20 , '00001' , 'Reading'         , 'Fail'     , '20160930',  5   Union all
SELECT 21 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT 22 , '00001' , 'Writing'         , 'Fail'     , '20160608',  7   Union all
SELECT 23 , '00001' , 'Writing'         , 'Fail'     , '20160930', 10   Union all
SELECT 24 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT 25 , '00001' , 'Social Studies'  , 'Fail'     , '20160930', 10   Union all
SELECT 26 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT 27 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT 28 , '00001' , 'Reading'         , 'Fail'     , '20160930',  5   Union all
SELECT 29 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT 30 , '00001' , 'Writing'         , 'Fail'     , '20160608',  7   Union all
SELECT 31 , '00001' , 'Writing'         , 'Fail'     , '20160930', 10   Union all
SELECT 32 , '00001' , 'Social Studies'  , 'Fail'     , '20160608',  7   Union all
SELECT 33 , '00001' , 'Social Studies'  , 'Fail'     , '20160930', 10   Union all
SELECT 34 , '00001' , 'Science'         , 'Fail'     , '20160608',  8   Union all
SELECT 35 , '00001' , 'Reading'         , 'Fail'     , '20160608',  2   Union all
SELECT 36 , '00001' , 'Reading'         , 'Fail'     , '20160930',  5   Union all
SELECT 37 , '00001' , 'Math'            , 'Fail'     , '20160608',  8   Union all
SELECT 38 , '00001' , 'Writing'         , 'Pass'     , '20160608',  7   Union all
SELECT 39 , '00001' , 'Writing'         , 'Pass'     , '20160930', 10   Union all
SELECT 40 , '00001' , 'Social Studies'  , 'Pass'     , '20160608',  7   Union all
SELECT 41 , '00001' , 'Social Studies'  , 'Pass'     , '20160930', 10   Union all
SELECT 42 , '00001' , 'Science'         , 'Pass'     , '20160608',  8   Union all
SELECT 43 , '00001' , 'Reading'         , 'Pass'     , '20160608',  2   Union all
SELECT 44 , '00001' , 'Reading'         , 'Pass'     , '20160930',  5   Union all
SELECT 45 , '00001' , 'Reading'         , 'Pass'     , '20161202',  9  Union all
SELECT 46 , '00001' , 'Math'            , 'Pass'     , '20160608',  8 
)
SELECT id
    ,EXAMNE_ID
    ,TestName
    ,PASS_STA
    ,TST_DTE
    ,STD_SCOR
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY TestName ORDER BY id DESC
            ) AS Rno
    FROM (
        SELECT *
            ,ROW_NUMBER() OVER (
                PARTITION BY TestName
                ,TST_DTE ORDER BY id DESC
                ) AS Seq
        FROM cte
        ) Dt
    WHERE Dt.Seq = 1
        AND PASS_STA = 'Pass'
    ) dt2
WHERE dt2.Rno = 1
ORDER BY dt2.id ASC

OutPut

+----+-----------+-----------------+----------+----------+----------+
| id | EXAMNE_ID |    TestName     | PASS_STA | TST_DTE  | STD_SCOR |
+----+-----------+-----------------+----------+----------+----------+
| 39 |     00001 | Writing         | Pass     | 20160930 |       10 |
| 41 |     00001 | Social Studies  | Pass     | 20160930 |       10 |
| 42 |     00001 | Science         | Pass     | 20160608 |        8 |  
| 45 |     00001 | Reading         | Pass     | 20161202 |        9 |
| 46 |     00001 | Math            | Pass     | 20160608 |        8 |
+----+-----------+-----------------+----------+----------+----------+

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Other than the datatype issue, you need to remove std_scor from your group by, and move your having to where:

Converting the tst_dte to date or int is not necessary given your date format, but I included the conversion anyway.

select 
   examne_id
 , pass_sta
 , TestName
 , max(convert(date,tst_dte)) as tst_dte
 , max(convert(int,std_scor)) as std_scor
from dbo.TestScores
group by 
   examne_id
 , pass_sta
 , TestName
having pass_sta = 'Pass'

rextester demo: http://rextester.com/BDI16678

returns:

+-----------+----------+----------------+------------+----------+
| examne_id | pass_sta |    TestName    |  tst_dte   | std_scor |
+-----------+----------+----------------+------------+----------+
|         1 | Pass     | Math           | 2016-06-08 |        8 |
|         1 | Pass     | Reading        | 2016-12-02 |        9 |
|         1 | Pass     | Science        | 2016-06-08 |        8 |
|         1 | Pass     | Social Studies | 2016-09-30 |       10 |
|         1 | Pass     | Writing        | 2016-09-30 |       10 |
+-----------+----------+----------------+------------+----------+

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Rank your records per student and test by date with ROW_NUMBER. Then keep only the record with the latest date per student and test (i.e. the records ranked #1).

select examne_id, pass_sta, testname, tst_dte, std_scor
from
(
  select 
    ts.*, 
    row_number() over (partition by examne_id, testname order by tst_dte desc) as rn
  from dbo.testscores ts
  where pass_sta = 'Pass'
)
where rn = 1;

Upvotes: 0

Related Questions