0

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
Massi
  • 57
  • 7
  • Use Conditional Aggregation. – Thom A Feb 07 '22 at 12:00
  • Does this answer your question? [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) – pringi Feb 07 '22 at 12:16

1 Answers1

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Is type really a reserved word? – jarlh Feb 07 '22 at 12:09
  • Looks like OP wants MIN. – Serg Feb 07 '22 at 12:10
  • @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. – Aaron Bertrand Feb 07 '22 at 12:37
  • 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. – Charlieface Feb 07 '22 at 12:59
  • 1
    @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… – Aaron Bertrand Feb 07 '22 at 13:05
  • I see, noticed year finally ) – Serg Feb 07 '22 at 13:10
  • @AaronBertrand, the result of my first code(User Type LastDate) is a join from 3 table (`inner join tableB.Type on TableC.Type`), but I get error "Column [Type] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I use Type only statement "CASE"... don't understand – Massi Feb 07 '22 at 14:11
  • @Massi Please don't simplify your question. How can anyone possibly troubleshoot code they can't see or provide the right answer to the wrong question? – Aaron Bertrand Feb 07 '22 at 14:14
  • @AaronBertrand you are completely right, I wanted to simplify to make the question clearer, but I was wrong – Massi Feb 07 '22 at 15:07
  • @Massi Ok, I solved the problem pointed out in the question, I think if you want to make it more complex at this point you should create a new question. – Aaron Bertrand Feb 07 '22 at 15:25
  • @AaronBertrand, I will do it. Thanks! – Massi Feb 07 '22 at 15:59
  • I have updated my question! – Massi Feb 08 '22 at 14:32
  • @Massi The relationship between these tables doesn't make sense to me. But since you have one row per user, what IdType do you want on each row and why? – Aaron Bertrand Feb 08 '22 at 15:09
  • @AaronBertrand I need to get a row for each user assigning (last)DateCourse to dateInsert when IdType = 1 and to dateUpdate when IdType = 2 – Massi Feb 10 '22 at 11:13
  • @Massi I still don't understand why (or how) you're trying to add IdType to the output (and it doesn't help that your fiddle is broken, and even if it worked, it is not "ok" like you say in your question, because it doesn't have `c.IdType` in the select list). You know which one is type 1 and which one is type 2 because of which column it is (they're named `DateType1` and `DateType2`). When you have type 1 date in one column and type 2 date in the other column, on the same row, what value could you possibly want in an IdType column? – Aaron Bertrand Feb 10 '22 at 16:58
  • @AaronBertrand I don't want idType in the output... I finally solved it like this: ` SELECT [User], MAX(CASE WHEN [Type] = 1 THEN LastDate END) AS DateType1, MAX(CASE WHEN [Type] = 2 THEN LastDate END) AS DateType2 FROM dbo.tablename GROUP BY [User];` – Massi Feb 10 '22 at 17:29
  • @Massi The query you added to the question after accepting my answer started with `SELECT d.IDUser, c.IdType,` ... if you don't want `IdType` in the output, why did you add it to the select list? Also how does what you just typed in that comment differ from the solution I posted in the answer three days ago? – Aaron Bertrand Feb 10 '22 at 17:32
  • @AaronBertrand because I'm an idiot ... I added it by mistake because I was doing tests on my pc! – Massi Feb 10 '22 at 17:35
  • @AaronBertrand your solution on sqlfiddle works (until yesterday sqlfiddle supported sql server ... that's why today my sqlfiddle doesn't work) but on my db it was in error (Column 'TableCourse.IdType' is invalid in the select list ...). Changing position to MAX (...) also works on my db – Massi Feb 10 '22 at 17:50