Avatar of Star79
Star79
Flag for United States of America asked on

Query Help-Top Row for multiple selection

Hello All,
I have the below query,is there a way to return the top 1 row for each facility ie '261','262','40'.
Thank you.

 select   FacName,rxs.FacID,rxs.PatID, rxs.rxno,rxs.PhNPI,PhName,NsID,Room,patients.PatFName,patients.PatLName,rxs.DrugLabelName,sig,
 CardIDNo 
 , InsName
 ,errors.SeqNo
 ,errors.RejectCd
 ,errors.Submitted
,"ECSRejectCds"."Description"
,ECSRejectCds.ECSRejectCd
 --
 from Rxs
 left join Facilities on rxs.FacID = Facilities.FacID
 left join Patients on rxs.PatID = patients.PatID
left join vw_crystal_ReorderDirectionsExpandedSig vw on rxs.FacID = vw.FacID and rxs.RoNo = vw.RoNo and rxs.PatID = vw.PatID
left  join PatientMOPs on rxs.PatID = PatientMOPs .PatID and rxs.FacID = PatientMOPs.FacID and rxs.MOP = PatientMOPs.InsID 
left join InsPlans on rxs.MOP = insplans.InsID
left join ECSHistoryErrors errors on rxs.FacID = errors.FacID and rxs.PatID = errors.PatID and rxs.RxNo = errors.RxNo
left JOIN "FwReports"."dbo"."ECSRejectCds" "ECSRejectCds" ON errors."RejectCd"="ECSRejectCds"."ECSRejectCd"
 where  "Rxs"."RxBatch"='TP-RPH'
 and   CardIDNo is not null 
 and ECSRejectCds.Ver='D.0'
 and rxs.FacID in('261','262','40') 
  order by Submitted desc ,SeqNo desc

Open in new window

Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Star79

8/22/2022 - Mon
David L. Hansen

SELECT TOP 1 * FROM myTable WHERE.....

This returns just the first row from whatever query you use.
David L. Hansen

You literally just need to put in "TOP 1 " right after the "SELECT" word and that's it (no comma).
Star79

ASKER
the top 1 would return he top most row  for a facility based on the submitted date and seqNo.But is there a way to retirve the top most row for each facility id(facid in the query)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
David L. Hansen

I would just put the desired ID in the WHERE statement and then use the top 1 request.  Do this for each ID you want.  Do you need all top 1's in one query?
Star79

ASKER
I need all the top 1's in one query
David L. Hansen

Can you give a simple sample of your data and show which rows you want the query to pull?  Do you care which top 1 you get or would any with the target id do?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Star79

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Star79

ASKER
The above did what was needed