SQL Query --> Only records where...

Hey Experts,

I have the following query, where I would like to include only one instance for every customernumber, namely the one with the earliest "bookingTime".

How do I extend the query to include this requirement?

Best regards, Raahaugen

SQL Query:

SELECT
M.Id
,M.RebookedMeetingId
,M.BookingTime
,M.StartTime
,M.EndTime
,M.Status
,M.MeetingPurpose
,MT.Name as MeetingType
,M.CreatedBy as Booker
,TA.Initials as TiedAgent
,C.Name as Center
,CUS.CustomerNumber
,ADR.ZipCode

FROM "MBW-DB"."dbo"."Meeting" M
join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id

WHERE M.MeetingPurpose = 'SalesMeeting'

order by BookingTime asc

Microsoft ExcelSQL

Last Comment

Raahaugen

8/22/2022 - Mon

Randy Downs

7/1/2012

Try select distinct instead of select

SELECT DISTINCT
M.Id
,M.RebookedMeetingId
,M.BookingTime
,M.StartTime
,M.EndTime
,M.Status
,M.MeetingPurpose
,MT.Name as MeetingType
,M.CreatedBy as Booker
,TA.Initials as TiedAgent
,C.Name as Center
,CUS.CustomerNumber
,ADR.ZipCode

Olaf Doschke

7/1/2012

Don't know whther Execel support subqueries, but tht would solve it:

WHERE M.customerId,M.BookingTime IN
(Select customerId, Min(BookingTime) FROM "MBW-DB"."dbo"."Meeting"
Group By customerId WHERE MeetingPurpose = 'SalesMeeting')

Bye, Olaf.

Lowfatspread

7/2/2012

you are connecting to an external database?
what is its dbms?

you either need to make use of the OVER windowing clause if your version of sql supports it
or use a subquery/join to obtain the desired result....

however the row_number() Over technique is simplest if multiple rows could exist with the same latest "timestamp"

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Lowfatspread

7/2/2012

what is the reationship between customerid and customer_number?

1:1
or 1:M ?

Lowfatspread

7/2/2012

an example using row_number() over for Sql server (from sql 2005 onwards)

SELECT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
from (
SELECT
             M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
   ,row_number() over (partition by cus.customernumber 
                        order by m.bookingtime asc) as rn

FROM "MBW-DB"."dbo"."Meeting" M 
            join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
            join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
            join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
            join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
            join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id
           
WHERE M.MeetingPurpose = 'SalesMeeting'
) as x
where x.rn=1
order by BookingTime asc

Open in new window

Lowfatspread

7/2/2012

sorry forgot to resolve the aliases...

SELECT Id
            ,RebookedMeetingId
            ,BookingTime
            ,StartTime
            ,EndTime
            ,Status
            ,MeetingPurpose
            , MeetingType
            , Booker
            , TiedAgent
            , Center
            ,CustomerNumber
            ,ZipCode
            
from (
SELECT M.Id
            ,M.RebookedMeetingId
            ,M.BookingTime
            ,M.StartTime
            ,M.EndTime
            ,M.Status
            ,M.MeetingPurpose
            ,MT.Name as MeetingType
            ,M.CreatedBy as Booker
            ,TA.Initials as TiedAgent
            ,C.Name as Center
            ,CUS.CustomerNumber
            ,ADR.ZipCode
            
   ,row_number() over (partition by cus.customernumber 
                        order by m.bookingtime asc) as rn

FROM "MBW-DB"."dbo"."Meeting" M 
            join "MBW-DB"."dbo"."TiedAgent" TA on M.TiedAgentId = TA.id
            join "MBW-DB"."dbo"."MeetingType" MT on M.meetingTypeId = MT.id
            join "MBW-DB"."dbo"."Center" C on TA.centerId = C.Id
            join "MBW-DB"."dbo"."Customer" CUS on M.customerId = CUS.Id
            join "MBW-DB"."dbo"."Address" ADR on M.addressId = ADR.Id
           
WHERE M.MeetingPurpose = 'SalesMeeting'
) as x
where x.rn=1
order by BookingTime asc

Open in new window

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

Lowfatspread

7/2/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Raahaugen

7/15/2012

ASKER

Hey!

I asked our developer, who simply told me that I should go for every row in wich rebookedMeeting is 0. So I did - and you guys had no way to know that!!

Anyways your solutions inspired me to do some other queries, so you absolutely desserve some points.

I hope this will work out?

Best regards Raahaugen

SQL Query --&gt; Only records where...

SQL Query:

SQL Query --> Only records where...