Avatar of Star79
Star79Flag 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,
 , InsName
 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
Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

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

This returns just the first row from whatever query you use.
You literally just need to put in "TOP 1 " right after the "SELECT" word and that's it (no comma).
Avatar of Star79
Flag of United States of America image


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)
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?
Avatar of Star79
Flag of United States of America image


I need all the top 1's in one query
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?
Avatar of Star79
Flag of United States of America image

Blurred text
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
Avatar of Star79
Flag of United States of America image


The above did what was needed
Microsoft SQL Server 2008
Microsoft SQL Server 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo