Exclude single valuse results in a Query

I have a query where I only want to display the record if there is more than one in the arrival order. They have a common "EID" (Event Identification) number. I only want the ones where there are more than one in the arrival order, but I still need the first one as well. So I cannot filter or exclude the ones that have an arrival number that = 1.  

The highlighted fields are the type I want to show - Only rows where there is more than one record that have the same "EID"  number. I need to exclude all the ones that return a single record only. Hopefully that makes sense...

Sample Query
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
After making a temp table #t with some data, I have made two CTEs.
d partitions data per CAD_EID to number them as 1, 2, 3, etc.
e, the second CTE, inner joins d with #t to pull the necessary data for the outer query below it with condition that there are more than 1 records per CAD_EID.
;With d
Select row_number()  over(partition by CAD_EID order by ArrivalOrder DESC) As rn
From #t),
Select #t.CAD_EID, ArrivalOrder, d.rn 
From #t inner join d on d.CAD_EID = #t.CAD_EID
Where d.rn >=2)
Select e.CAD_EID, e.ArrivalOrder
From e;

Open in new window

This returns the following (base on the sample data in #t defined below):
963226      2
963226      1
963232      2
963232      1

create table #t(CAD_EID int, ArrivalOrder int)
Insert into #t(CAD_EID, ArrivalOrder) Values
(963233, 1)
, (963232, 2)
, (963232, 1)
, (963227, 1)
, (963226, 2)
, (963226, 1)
, (963224, 1);

Select * From #t;

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Using the same test data to keep it simple:
select CAD_EID, ArrivalOrder
from (
  select *, cnt = count(*) over (partition by CAD_EID) from #t
) d where cnt > 1;

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
Some explanation for the future readers as to why much simpler solution from Qlemo works:

Here we have basically:
From ...
Where ...

But the logical sequence of execution is:

From ...         this means  cnt is determined first and it is available for use in the where clause following it.
Where ...  

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

srodgers45Author Commented:
How would I add these to the query to get the results?

Do I need to create a new table to query the data?

Current Query:

SELECT     TOP (100) PERCENT [Incident Date], [Job for Date], [CAD EID], ArrivalOrder, ArrivalTime, [PCR ID Number]
FROM         Extract.Trips
WHERE     ([Incident Date] > CONVERT(DATETIME, '2015-05-31 00:00:00', 102)) AND (ArrivalTime IS NOT NULL)

Thanks , I really appreciate the help, this is above my SQL abilities....
Mike EghtebasDatabase and Application DeveloperCommented:

Your original question is already answered. For new question you may have, please post a new question.


Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The TOP clause is superfluous here. You are selecting all records anyway.

You integrate the suggestion into your question this way - note that you have to move different parts of the original query to different places:
select  [Incident Date], [Job for Date], [CAD EID], ArrivalOrder, ArrivalTime, [PCR ID Number]
from (
  select *, cnt = count(*) over (partition by [CAD EID])
  from Extract.Trips
  where [Incident Date] > CONVERT(DATETIME, '2015-05-31 00:00:00', 102)
    and ArrivalTime IS NOT NULL
) d where cnt > 1;

Open in new window

Is it essential that the WHERE clause is applied to the inner SELECT, if you want to only consider rows matching that condition. Above will only show rows which have more than one row after that start time.

If it is your intention to count rows for all CAD EID, but of those having more than one row only get what is after 2015-05-31, we would have to move the condition to the outer SELECT.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srodgers45Author Commented:
All due respect it is only half answered as I do not know how to apply it to achieve the desired results. That is why I asked the question to begin with. I am not a sequel developer. If you want more points I can accept and re-ask the question. I am sure you answer is 100% correct.

Thank for your assistance.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
srodgers45, I agree. Your follow-up question is valid and necessary for applying what has been shown. I hope you've seen my reply above to that regard.
Mike EghtebasDatabase and Application DeveloperCommented:

I do not expect any points for this questions because Qlemo has the best answer. Usually, after the original question comes one or two followup question and goes on and on.

From the response Qlemo has provided, I am learning it is okay to have a follow up question. I thought the follow ups are asked in a separate thread because it makes it easier for the future readers in filtering.


srodgers45Author Commented:
Thank you very much for the assistance. I am still learning all of this stuff and will never have all the answers. Take Care
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.