Don't get max date values from one column from multiple rows in 2 columns because column is invalid in the select list [duplicate]

Question

I must get max date values from one column from multiple rows in 2 columns based on IdType in one rows for IDUser.

I simplify my tables like this :

TableDoc
IDUser  IdCourse
John    C
Jeff    E
Michael F
Tom     A
John    E
Jeff    C
Michael A
Tom     E

TableCourse
IdCourse IdType DateCourse
A        2      2020-07-31 00:00:00
C        2      2019/06/06 00:00:00
C        1      2021/04/14 00:00:00
E        2      2021/04/29 00:00:00
E        2      2020/06/09 00:00:00
F        1      2020/06/25 00:00:00
F        2      2021/04/09 00:00:00
F        2      2020/06/01 00:00:00

I must get this:


IDUser      DateInsert              DateUpdate
Jeff        2021-04-14 00:00        2021-04-29 00:00
John        2021-04-14 00:00        2021-04-29 00:00
Michael     2020-06-25 00:00        2021-04-09 00:00 
Tom         null                    2021-04-29 00:00

This is my query:

SELECT d.IDUser, c.IdType,
DateInsert = MAX(CASE WHEN c.IdType = 1 THEN DateCourse END),
DateUpdate = MAX(CASE WHEN c.IdType = 2 THEN DateCourse END)
FROM dbo.TableDoc d
inner join TableCourse c
on d.IdCourse = c.IdCourse
GROUP BY IDUser;

In [Sql Fiddle][1] is ok, but in Sql Server Management (with my real tables) I have this error:

Column 'TableCourse.IdType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.```

What could be the problem?


  [1]: http://sqlfiddle.com/#!18/9c982/3

Does this answer your question? Get records with max value for each group of grouped SQL results — pringi
– pringi, Commented Feb 7, 2022 at 12:16

Aaron Bertrand · Accepted Answer · 2022-02-07 12:02:33Z

3

It's tempting to think abut PIVOT but in this case conditional aggregation is better IMHO:

SELECT [User], 
  DateType1 = MAX(CASE WHEN [Type] = 1 THEN LastDate END),
  DateType2 = MAX(CASE WHEN [Type] = 2 THEN LastDate END)
FROM dbo.tablename GROUP BY [User];

I also recommend you stay away from special words like User and Type because they force you to add these gross [square brackets] all over the place.

edited Feb 7, 2022 at 12:02

answered Feb 7, 2022 at 12:00

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

jarlh Over a year ago

Is type really a reserved word?

Serg Over a year ago

Looks like OP wants MIN.

Aaron Bertrand Over a year ago

@jarlh I didn't say it was a reserved word but since you can CREATE TYPE, and because it lights up in IntelliSense (unless you delimit it), yes I would still stay away from it. It's not descriptive enough anyway IMHO.

Charlieface Over a year ago

Have you ever actually come across a case where PIVOT actually makes sense over manual aggregation? The only time I ever use it is with very long pivot lists, such as pivoting on row-numbers.

Aaron Bertrand Over a year ago

@charlie No, and I find the syntax hard to remember sometimes to boot, but it is less tedious if the list is long like you said. And people often choose PIVOT first until it doesn’t do what they need anymore (or requires multiple), so I’m not sure exactly what the draw is. Maybe similar to MERGE, it’s new so it must be better, just beware the dragons…

|

Collectives™ on Stack Overflow

Don't get max date values from one column from multiple rows in 2 columns because column is invalid in the select list [duplicate]

1 Answer 1

19 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Linked

Related