Avatar of Jeff S
Jeff SFlag for United States of America asked on

SQL 2000 - Order by Question.

Please note, there are items in my where clause that will look odd to you .... please disregard them, the SQL is handled dynamically by the SQL server.

My client has requested to order the fac.listname in a distinct manner. Is this possible? I know I can do it in ASC and DESC order, but not by a specific manner. Any help is appreciated. If possible, could I get it in a seperate field so I can use that field in Crystal to Group off .... ??

Special order for field fac.ListName. All Others are discarded:

1H - Hillman
2V - Visalia
3L - Lindsay
4D - Dinuba
5F - Farmersville
6P - Porterville
7H - Tulare Peds
SELECT DISTINCT 
	crii.PP_Last, 
	crii.PP_First, 
	crii.PP_Middle, 
	crii.PP_Birthdate, 
	crii.PP_Address1, 
	crii.PP_Phone1, 
	pvp.DateOfServiceFrom, 
	pp.MedicalRecordNumber, 
	pvd.ICD9Code, 
	fac.ListName as Facility, 
	df.ListName as Doctor
FROM   
	PatientVisit pv 
	INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId 
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
	INNER JOIN cusCRIInterview crii ON pv.PatientProfileId = crii.PatientProfileID 
	INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId 
	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
	INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
WHERE
	( 
	(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
	(NULL IS NULL) 
	)
	AND 
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND 
	(
	pvp.DateOfServiceFrom >= ISNULL('01/01/2008','1/1/1900') AND
	pvp.DateOfServiceFrom < dateadd(month, 1, ISNULL('01/29/2009','1/1/3000'))
	)

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Jeff S

8/22/2022 - Mon
Aneesh

SELECT DISTINCT
      crii.PP_Last,
      crii.PP_First,
      crii.PP_Middle,
      crii.PP_Birthdate,
      crii.PP_Address1,
      crii.PP_Phone1,
      pvp.DateOfServiceFrom,
      pp.MedicalRecordNumber,
      pvd.ICD9Code,
      fac.ListName as Facility,
      df.ListName as Doctor
FROM  
      PatientVisit pv
      INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
      INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
      INNER JOIN cusCRIInterview crii ON pv.PatientProfileId = crii.PatientProfileID
      INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
      INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
      INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
WHERE
      (
      (NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      pvp.DateOfServiceFrom >= ISNULL('01/01/2008','1/1/1900') AND
      pvp.DateOfServiceFrom < dateadd(month, 1, ISNULL('01/29/2009','1/1/3000'))
      )

ORDER BY  fac.listname ASC
ASKER
Jeff S

Special order for field fac.ListName. All Others are discarded:

1H - Hillman
2V - Visalia
3L - Lindsay
4D - Dinuba
5F - Farmersville
6P - Porterville
7H - Tulare Peds
I only want these facilities ... I have others I need to exclude.
chapmandew

This is a very interesting WHERE statement:

      (
      (NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
      (NULL IS NULL)
      )

What in the world are you trying to do here?  Can't you just do this:

pv.DoctorID IS NULL OR pv.FacilityId IS NULL
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
BrandonGalderisi

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.
See how we're fighting big data
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
ASKER
Jeff S

Chapmandew ....
Please read my original post ... I know it looks odd ... its handled dynamically.
ASKER
Jeff S

Brandon -
I think your on the right track ... testing it all now. Why the Order by 12? What is 12?
chapmandew

Whoops...sorry about that.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BrandonGalderisi

12 is the 12th column.
BrandonGalderisi

You can order by "column #" when it is an alias, or the column name appears twice in the select statement.  For example:

Let's say you have a 50 column table and you want to order by, and see the right most column on the far left.

select Value1,* from MyTable
order by value1

That will produce an error about ambiguous column name because value1 is in the * as well.  But who wants to list out ALL 50 columns in the select list

select Value1,* from MyTable
order by 1

You can use the "order by #" syntax where # is the position of the desired sort column in the select list.
ASKER
Jeff S

chapmandew ....
Trust me ... I hate it too and its a pain to mess with. Its also something I have to explain every time I post for help.
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
ASKER
Jeff S

Brandon -
You just taught me a cool trick ... very cool. I'll let you know on my testing how it goes.
Raja Jegan R

Try out the below code tuned to some extent:

Uncomment the two places as required.
SELECT 
--DISTINCT 
        crii.PP_Last, 
        crii.PP_First, 
        crii.PP_Middle, 
        crii.PP_Birthdate, 
        crii.PP_Address1, 
        crii.PP_Phone1, 
        pvp.DateOfServiceFrom, 
        pp.MedicalRecordNumber, 
        pvd.ICD9Code, 
        CASE when fac.ListName = 'Hillman' THEN '1H'
             when fac.ListName = 'Visalia' THEN '2V'
             when fac.ListName = 'Lindsay' THEN '3L'
             when fac.ListName = 'Dinuba' THEN '4D'
             when fac.ListName = 'Farmersville' THEN '5F'
             when fac.ListName = 'Porterville' THEN '6P'
             when fac.ListName = 'Tulare Peds' THEN '7H'
         ELSE NULL
         END AS Facility, 
        df.ListName as Doctor
FROM   
        PatientVisit pv 
        INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId 
        INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
        INNER JOIN cusCRIInterview crii ON pv.PatientProfileId = crii.PatientProfileID 
        INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId 
        INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
WHERE pv.FacilityId is NULL
  AND pv.DoctorID IS NULL
--  AND fac.ListName in ('Hillman', 'Visalia', 'Lindsay', 'Dinuba', 'Farmersville', 'Porterville','Tulare Peds')
  AND pvp.DateOfServiceFrom >= '01/01/2008'
  AND pvp.DateOfServiceFrom < dateadd(month, 1,'01/29/2009')
order by 10

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Jeff S

Scott,
I learned yet another cool thing.
"You don't have to actually SELECT that value to sort by it :-) ."
I am now getting this message. If I remove the DISTINCT keyword I get results, but I need them Distinct.
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

OOPS, sorry, forgot about that.  If you specify DISTINCT, yes, you will have to SELECT the value.
ASKER
Jeff S

Thanks!