MaxMin Function within Select Statement SQL 2012

Question

I am having a few issues making a MAX function work within the select statement See example data below:

Table 1                   Table 2
Visit_ID Car_ID        Move_ID  Visit_ID  MoveStartDate  MoveEndDate
A          1              1        A      25/07/2016     27/07/2016 
B          2              2        A      28/07/2016     28/07/2016   
C          1              3        B      19/07/2016     22/07/2016 
D          3              4        D      28/06/2016     30/06/2016

I would like my select statement to pick the min start time and Max start time based on the Visit_ID so I would be expecting:

Result
Visit_ID   Car_ID  StartDate   EndDate
A           1      25/07/2016  28/07/2016
B           2      19/07/2016  22/07/2016

So far I have tried I already have Inner Joins in my select statement:

,(MAX (EndDate) WHERE Visit.Visit_ID = Move.Visit_ID) AS End Date

I have looked at some other queries with a second select statement within the select so you end up with something like:

Select Visit_ID, Car_ID ,(Select MAX(EndDate) FULL OUTER JOIN Table 2 ON Table 1.Visit_ID = Table 2.Visit_ID Group By Table 1.Visit_ID) AS End Date

Hope I have provided enough info currently stumped.

Possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — GSerg, Jul 28 '16 at 09:02
Consider adding to the query the difference (end-start) making an outer select that would pick the max and min of that difference. — FDavidov, Jul 28 '16 at 09:04
@jarlh Yes I meant to include that in the result. This is only a snippet of a table. — Dunny774, Jul 28 '16 at 09:49
@Dunny774, can't you adjust the expected result, so it reflects the sample data? — jarlh, Jul 28 '16 at 10:45

score 1 · Answer 1 · answered Jul 28 '16 at 09:10

1

If you also want Car_ID = 3 in the result:

select t1.Visit_ID, t1.Car_ID, MIN(MoveStartDate), MAX(MoveEndDate)
from table1 t1
  join table2 t2 on t1.Visit_ID = t2.Visit_ID
group by t1.Visit_ID, t1.Car_ID

Returns:

SQL>select t1.Visit_ID, t1.Car_ID, MIN(MoveStartDate), MAX(MoveEndDate)
SQL&from table1 t1
SQL&  join table2 t2 on t1.Visit_ID = t2.Visit_ID
SQL&group by t1.Visit_ID, t1.Car_ID;

visit_id      car_id
======== =========== ==================== ====================
A                  1 25/07/2016           28/07/2016
B                  2 19/07/2016           22/07/2016
D                  3 28/06/2016           30/06/2016

                  3 rows found

answered Jul 28 '16 at 09:10

jarlh

42,561
8
45
63

Thanks for the response unfortunately I am seeing Column errors of 'column name' is invalid in the select list because it is not contained in either aggregate function or the group by clause. – Dunny774 Jul 28 '16 at 09:44
@Dunny774, very odd. Are you really executing my SELECT, or something similar/adjusted? – jarlh Jul 28 '16 at 10:43
I have managed to get the subquery to work but with a new error of subquery returning more than one value – Dunny774 Jul 28 '16 at 15:04
Subquery? Is this just a small part of a bigger query? (This is an answer to the question asked above. If you want to know something else, ask another question.) – jarlh Jul 29 '16 at 06:29
What I have down at the moment: – Dunny774 Jul 29 '16 at 08:48
What I have down at the moment: 'Select t1.Car_id, t1.Car_name, t1.Car_Reg ,(Select Min(t2.MoveEndDate) from dbo.move as t2 Inner Join t1 on t2.Visit_id = t1.Visit_ID' Where t2.Visit_ID = t1.Visitid) As 'Arrive time' At the moment it complains that it will return multiple values, if I put Select top 1 it returns the record but in pairs where one with the field complete and other as null. – Dunny774 Jul 29 '16 at 08:58
Move the sub-query from the `SELECT` list to the `FROM` clause. I.e. do a `JOIN` instead! – jarlh Jul 29 '16 at 09:05
Could you please provide an example I do not understand what you mean? – Dunny774 Jul 29 '16 at 09:39
Ask another question. Showing us the whole query that doesn't work. – jarlh Jul 29 '16 at 09:43
1

I'll put another question on with more complete code and stuff on – Dunny774 Jul 29 '16 at 09:51
Please see: [link](http://stackoverflow.com/questions/38656419/min-max-subquery-issue?noredirect=1#comment64694030_38656419) – Dunny774 Jul 29 '16 at 10:35

Dudi Konfino · Answer 2 · 2016-07-29T06:56:54.667

1

I did not check it but your can try this

WITH cte
AS
(select Move_ID,Visit_ID,min(MoveStartDate) AS mMS,MAX(MoveEndDate) AS mME
 FROM Table_2
 GROUP BY Move_ID,Visit_ID)

 SELECT c.Move_ID,c.Visit_ID,T1.Car_ID,c.mMS,c.mME
 FROM Table_1 as T1 JOIN cte as C
 ON c.Visit_ID=T1.Visit_ID

edited Jul 29 '16 at 06:56

answered Jul 29 '16 at 06:32

Dudi Konfino

1,126
2
13
24

MaxMin Function within Select Statement SQL 2012

2 Answers2