SQL Max() Returning multiple values

Question

Good morning/afternoon & thank you for checking on this, I'm struggling to figure out what I'm missing.

Attempting to put together a schedule system off the results (separate task) but because our schedules change several times a day/week its creating too many duplicates.

Select modified, local_Start_Time, local_End_Time, seg_code, Date, employee

from schedule_table

Where employee= '###' and seg_code = ‘schedule’
and Date in ('20160606', '20160607', '20160608', '20160609') 

group by modified
,local_start_time
,local_end_time
,seg_code
,date
,employee

When I run this, I get this Table as expected.

I tried adding max () around the 'modified' select to limit it to the latest entry, but that did not change anything from the above. I then added

 max(Local_START_Time)
,max(Local_END_time)
,max(modified)

to the selects, which did limit it to 1 line per employee however its now taking the opposite entries from what it should per this [table 2][2]

I tried adjusting it from max to min on the start/end times and it fixed some and broke others.

I'm fairly new to SQL and have tried looking at other questions/videos but to no avail. I understand the logic where it's taking the 'max' modified as the most recent time entry, and then max (highest starting / end time) from those 2 cells but in a perfect world I'd prefer only one max value (modified) and then it pairs that to the start & end times.

Can anyone guide me in the right direction with what I'm missing to make this work either with 1 max statement, or if using all 3 / a different one entirely?

Using SQL Management Studio & apologies if I'm missing any other key details, thank you for your help and looking forward to learning more & helping others!

Edit To clarify the challenge I'm having, Updated Image, cells in green are both the latest updated schedule per date whereas the cells in red are what is being captured by the max(start/end time).

Hope that helps

ScaisEdge · Accepted Answer · 2016-06-08 19:15:00Z

1

If you want the max of the value you should perform the query without the group by you have setted for the column in aggregation function (max)

Select max(modified), max(local_Start_Time), max(local_End_Time), seg_code, Date, employee

from schedule_table

Where employee= '###' and seg_code = ‘schedule’
and Date in ('20160606', '20160607', '20160608', '20160609') 

group by seg_code
,date
,employee

for getting the row related to max(modified) you should instead use a subquery

    Select modified, local_Start_Time, local_End_Time, seg_code, Date, employee
    from schedule_table
    where modified = (  select max(modified)
                         from schedule_table
                         Where employee= '###' and seg_code = ‘schedule’
                        and Date in ('20160606', '20160607', '20160608', '20160609') 
    );

And for get only the employee then

    Select modified, local_Start_Time, local_End_Time, seg_code, Date, employee
    from schedule_table
    where modified = (  select max(modified)
                         from schedule_table
                         Where employee= '###' and seg_code = ‘schedule’
                        and Date in ('20160606', '20160607', '20160608', '20160609') 
    )
    and employee= '###'
    ;

edited Jun 8, 2016 at 19:15

answered Jun 8, 2016 at 16:49

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

6 Comments

Matt B Over a year ago

Thank you as well, as posted above it's still taking the highest start/end time from the table instead of the one that matches the actual modified time. Do you have any thoughts on how I can avoid that happening?

ScaisEdge Over a year ago

If i undertsand right you want obtain the start/end time related to max(modified) (most recent) time in table ?

Matt B Over a year ago

That is correct, otherwise it could take the latest update time & the first of the schedules that is now out of date.

Matt B Over a year ago

This looks like it should work, when I run the query it is returning the last edits to be made for the entire table instead of per person.but I'm continuing to play with it trying to correct that

ScaisEdge Over a year ago

The person is the employee?

|

Gordon Linoff · Accepted Answer · 2016-06-08 16:48:41Z

0

When you add the max(), you also need to remove the fields from the group by:

Select max(modified), max(local_Start_Time), max(local_End_Time),
       seg_code, Date, employee
from schedule_table
where employee= '###' and seg_code = 'schedule' and
      Date in ('20160606', '20160607', '20160608', '20160609') 
group by seg_code, date, employee;

Otherwise, the query returns a separate row for each value of the column -- and the max() will simply be the value in the row.

answered Jun 8, 2016 at 16:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Matt B Over a year ago

Thank you, updated that and it is returning the expected one line per day per employee however the challenge still exists for the table itself. It is taking the max (logically makes sense) schedule time which is going to be the highest assigned starting hour even though its not always accurate. Is there a different command to use instead?

gms08 · Accepted Answer · 2016-06-08 17:50:14Z

0

So here's what I got:

SELECT DISTINCT 
    [date], 
    employee, 
    max(modified) OVER (PARTITION BY [date]) AS max_modified,
    max(start_time) OVER (PARTITION BY [date]) AS max_start, 
    max(end_time) OVER (PARTITION BY [date]) AS max_end
FROM Table_1
GROUP BY 
    [date], employee, modified, start_time, end_time

OR if you wanted to separate out by date and modified date,

SELECT DISTINCT 
    [date], 
    employee, 
    max(modified) OVER (PARTITION BY [date],employee) AS max_modified, 
    max(start_time) OVER (PARTITION BY [date]) AS max_start, 
    max(end_time) OVER (PARTITION BY [date]) AS max_end
FROM Table_1
GROUP BY [date], employee, modified, start_time, end_time

answered Jun 8, 2016 at 17:50

gms08

13 bronze badges

Collectives™ on Stack Overflow

SQL Max() Returning multiple values

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related