Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Corresponding column value to MAX() of a different column? Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:56 PM
Points: 2, Visits: 19
Hopefully I can explain properly what I'm trying to do here. I'm not very experienced in SQL so maybe the solution is easier than I'd expect.

So let's say I have a Company table, so we have Company Names and UniqueIDs.

Then I have a table called Meetings, and this contains a Meeting Date, a Meeting Type, and the UniqueID for the Company the meeting was with.

So I want to select the most recent meeting with each company, so my three columns are Company, Meeting Date, and Meeting Type.

So I can join these tables, group by Company (its UniqueID), and use MAX(MeetingDate). This gives me the company and the date of the most recent meeting. Is there an easy way to pull in the MeetingType for whichever the MAX(MeetingDate) turned out to be?

I appreciate any help I can get, thanks!
Post #1593862
Posted Thursday, July 17, 2014 4:28 PM This worked for the OP Answer marked as solution


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 20,815, Visits: 32,749
Give this a try:


-- Then I have a table called Meetings, and this contains a Meeting Date, a Meeting Type, and the UniqueID for the Company
-- the meeting was with.
with BaseMeetings as (
select
MeetingDate,
MeetingType,
CompanyId,
row_number() over (partition by CompanyId order by MeetingDate desc) rn
from
dbo.Meetings
)
select
CompanyId,
MeetingType,
MeetingDate
from
BaseMeetings
where
rn = 1;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1593867
Posted Friday, July 18, 2014 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:56 PM
Points: 2, Visits: 19
Extremely helpful, I still have much to learn.
Post #1594138
Posted Friday, July 18, 2014 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 20,815, Visits: 32,749
Glad to help.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1594154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse