Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Getting the MAX() value of a field

Table and Expected Results

Hello Experts, please assist here.

Two tables involved here - dbo.myProductDevMeetings and dbo.MeetingStatus.

From the attached screen -shot, a "set" is defined as "records with the same BranchOfficeID, Date and StartTime"

Each "set" has an associated MeetingStatusID (various comobinations of some of or all of 1, 2, 3).

PROBLEM:
For a given set, trying to get the records with the most updated MeetingStatusID, eg
a. if the set has MeetingStatusIDs of 1, 2 and 3 - return the row/record that has MeetingStatusID of 3.
b. if the set has MeetingStatusIDs of 1 and 2 - return the row/record that has MeetingStatusID of 2

c. if the set has MeetingStatusIDs of  only 1  return the row/record that has MeetingStatusID of 1

d. if the set has MeetingStatusIDs of 1 and 3 - return the row/record that has MeetingStatusID of 3
...
etc

0
godwineffiong
Asked:
godwineffiong
  • 4
  • 2
  • 2
  • +1
1 Solution
 
godwineffiongAuthor Commented:
...From the screenshot - the records at the bottom marked "FINAL" show the expexted result.

Thank you!!
0
 
dwe761Commented:
what are the join field(s) between the two tables?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Try something like:
SELECT DISTINCT BranchOfficeID + ' ' + Date + ' ' + StartTime AS Set, MAX(MeetingStatusID) AS MeetingStatusID
FROM dbo.myProductDevMeetings INNER JOIN
dbo.MeetingStatus ON somecriteria
GROUP BY BranchOfficeID + ' ' + Date + ' ' + StartTime
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
SharathData EngineerCommented:
You need not to join with MeetingStatus if you want data from only the first table myProductDevMeetings.
If some of the columns are coming from MeetingStatus, then only you need a JOIN with MeetingStatus table.
You can try ROW_NUMBER like this.
select *
  from (select *,row_number() over (partition by BranchOfficeID, [Date],StartTime order by MeetingStatusID desc) as rn
          from myProductDevMeetings mp) t1
 where rn =1

Open in new window

0
 
godwineffiongAuthor Commented:
Table relationships is given below:

myProductDevMeetings.MeetingStatusID = MeetingStatus.MeetingStatusID

0
 
godwineffiongAuthor Commented:
@paulmacd: I am getting the following error:

Operand type clash: date is incompatible with int

Thanks!!
0
 
dwe761Commented:
Here's another way using sub-query:

SELECT p.*
FROM dbo.myProductDevMeetings p
INNER JOIN
(select BranchOfficeID, Date, StartTime, max(MeetingStatusID) AS MaxMeetingStatusID FROm dbo.myProductDevMeetings
group by BranchOfficeID, Date, StartTime
) t ON
      p.BranchOfficeID = t.BranchOfficeID AND
      p.Date = t.Date AND
      p.StartTime = t.StartTime AND
      p.MeetingStatusID = t.MaxMeetingStatusID
0
 
Paul MacDonaldDirector, Information SystemsCommented:
SELECT DISTINCT CAST(BranchOfficeID AS varchar) + ' ' + CAST(Date AS varchar) + ' ' + CAST(StartTime AS varchar) AS Set, MAX(MeetingStatusID) AS MeetingStatusID
FROM dbo.myProductDevMeetings INNER JOIN
dbo.MeetingStatus ON myProductDevMeetings.MeetingStatusID = MeetingStatus.MeetingStatusID
GROUP BY CAST(BranchOfficeID AS varchar) + ' ' + CAST(Date AS varchar) + ' ' + CAST(StartTime AS varchar)
0
 
godwineffiongAuthor Commented:
Wow!! I've never used "Row_Number()" or "Partition" before, but this works perfectly. Simple and elegant.

Thanks!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now