• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • 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
 
dwe761Software EngineerCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
dwe761Software EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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