Jeff S
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
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'))
)
ASKER
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.
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.
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
(
(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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Chapmandew ....
Please read my original post ... I know it looks odd ... its handled dynamically.
Please read my original post ... I know it looks odd ... its handled dynamically.
ASKER
Brandon -
I think your on the right track ... testing it all now. Why the Order by 12? What is 12?
I think your on the right track ... testing it all now. Why the Order by 12? What is 12?
Whoops...sorry about that.
12 is the 12th column.
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.
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
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.
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.
ASKER
Brandon -
You just taught me a cool trick ... very cool. I'll let you know on my testing how it goes.
You just taught me a cool trick ... very cool. I'll let you know on my testing how it goes.
Try out the below code tuned to some extent:
Uncomment the two places as required.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
OOPS, sorry, forgot about that. If you specify DISTINCT, yes, you will have to SELECT the value.
ASKER
Thanks!
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/1
pvp.DateOfServiceFrom < dateadd(month, 1, ISNULL('01/29/2009','1/1/3
)
ORDER BY fac.listname ASC