asked on 3/27/2013

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

Last Comment

Star79

4/1/2013

David L. Hansen

3/27/2013

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

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

David L. Hansen

3/27/2013

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

Star79

3/27/2013

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)

David L. Hansen

3/27/2013

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

3/27/2013

ASKER

I need all the top 1's in one query

David L. Hansen

3/27/2013

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?

ASKER CERTIFIED SOLUTION

Star79

3/27/2013

THIS SOLUTION IS 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

Star79

4/1/2013

ASKER

The above did what was needed