November 9, 2012 at 3:53 am
Hi,
I have an table called patientEncounter
I want to change the datetime to Varchar and it should be sorted order
select convert(varchar, enc_timestamp, 103) from patient_encounter
order by convert(varchar, enc_timestamp, 103) desc
If i ran this query,its not getting sorted by year(2012,2011)
Please someone help on this soon
31/10/2012
31/10/2012
31/10/2012
31/10/2012
31/10/2012
31/05/2012
31/05/2012
28/06/2011
25/05/2012
23/10/2012
23/05/2012
21/06/2012
21/06/2012
21/06/2012
21/06/2012
21/05/2012
20/06/2012
20/06/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
19/10/2012
18/05/2012
18/05/2012
18/05/2012
17/05/2012
17/05/2012
12/10/2012
11/10/2012
10/10/2012
09/10/2012
09/10/2012
09/10/2012
09/10/2012
08/11/2012
05/11/2012
05/11/2012
05/11/2012
05/11/2012
05/11/2012
05/06/2012
05/06/2012
02/11/2012
02/11/2012
02/11/2012
02/11/2012
01/06/2012
01/06/2012
Regards,
Tony
November 9, 2012 at 3:57 am
Try changing the order by clause to
ORDER BY enc_timestamp DESC
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 9, 2012 at 4:03 am
Koen Verbeeck (11/9/2012)
Try changing the order by clause to
ORDER BY enc_timestamp DESC
+1
As your ordering by a VARCHAR, it orders it from left to right not by value as that is the way that VARCHAR orders itself, so 3 is before 2, and 2 is before 1 and 1 is before 0
November 9, 2012 at 4:10 am
Please find attachedn abc.png..
still is not working with the query.
Please help me on this
November 9, 2012 at 4:13 am
Its picking up the column alias instead of the actual column as the sorting object.
Add the pe. alias to the order by or change the column alias in the select statement
November 9, 2012 at 4:15 am
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I am getting above error When am trying with alias in the order by.
SELECT distinct pd.description, convert(varchar(10),convert(datetime, pe.enc_timestamp, 103),103) , pd.icd9cm_code_id, dsm.description, pd.note
FROM ngkbm_dxcode_to_category_ ndc
join patient_diagnosis pd ON pd.icd9cm_code_id = ndc.txt_icd9cm_code_id
left join ngkbm_categry_to_specialty_ c2s ON ndc.disease_category = c2s.txt_disease_category
left join provider_mstr pm ON pm.specialty_code_1 = c2s.txt_Prov_specialty_code
left join diagnosis_status_mstr dsm ON dsm.status_id = pd.status_id
join patient_encounter pe on pe.enc_id = pd.enc_id
ORDER BY pe.enc_timestamp desc
November 9, 2012 at 4:25 am
Does the following work for you?
SELECT
Desc1,
convert(varchar(10),convert(datetime, enc_timestamp, 103),103) as EncTimestamp,
icd9cm_code_id,
Desc2,
note
FROM
(
SELECT
DISTINCT
pd.description Desc1,
pe.enc_timestamp,
pd.icd9cm_code_id,
dsm.description Desc2,
pd.note
FROM
ngkbm_dxcode_to_category_ ndc
join
patient_diagnosis pd
ON
pd.icd9cm_code_id = ndc.txt_icd9cm_code_id
left join
ngkbm_categry_to_specialty_ c2s
ON
ndc.disease_category = c2s.txt_disease_category
left join
provider_mstr pm
ON
pm.specialty_code_1 = c2s.txt_Prov_specialty_code
left join
diagnosis_status_mstr dsm
ON
dsm.status_id = pd.status_id
join
patient_encounter pe
on
pe.enc_id = pd.enc_id
) dev1
ORDER BY
dev1.enc_timestamp DESC
November 9, 2012 at 4:52 am
Anthony's solution should work, or you could simply alias the converted field and reference that in the Order By
Eg.
SELECT distinct
pd.description
, convert(varchar(10),convert(datetime, pe.enc_timestamp, 103),103) enc_timestamp
, pd.icd9cm_code_id
, dsm.description
, pd.note
FROM ngkbm_dxcode_to_category_ ndc
join patient_diagnosis pd ON pd.icd9cm_code_id = ndc.txt_icd9cm_code_id
left join ngkbm_categry_to_specialty_ c2s ON ndc.disease_category = c2s.txt_disease_category
left join provider_mstr pm ON pm.specialty_code_1 = c2s.txt_Prov_specialty_code
left join diagnosis_status_mstr dsm ON dsm.status_id = pd.status_id
join patient_encounter pe on pe.enc_id = pd.enc_id
ORDER BY enc_timestamp desc
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 9, 2012 at 4:56 am
Jason-299789 (11/9/2012)
Anthony's solution should work, or you could simply alias the converted field and reference that in the Order ByEg.
SELECT distinct
pd.description
, convert(varchar(10),convert(datetime, pe.enc_timestamp, 103),103) enc_timestamp
, pd.icd9cm_code_id
, dsm.description
, pd.note
FROM ngkbm_dxcode_to_category_ ndc
join patient_diagnosis pd ON pd.icd9cm_code_id = ndc.txt_icd9cm_code_id
left join ngkbm_categry_to_specialty_ c2s ON ndc.disease_category = c2s.txt_disease_category
left join provider_mstr pm ON pm.specialty_code_1 = c2s.txt_Prov_specialty_code
left join diagnosis_status_mstr dsm ON dsm.status_id = pd.status_id
join patient_encounter pe on pe.enc_id = pd.enc_id
ORDER BY enc_timestamp desc
Thats the OP's original query as part of the screenshot attached.
The problem lies with the order of logical query processing where order by is done after the select and as the column alias was the same as the column it was picking the column alias instead for ordering by the conversion instead of picking the column and ordering by datetime, hence the need for the sub select so that it can determin which enc_timestamp to order by.
November 9, 2012 at 5:00 am
anthony.green (11/9/2012)
Jason-299789 (11/9/2012)
Anthony's solution should work, or you could simply alias the converted field and reference that in the Order ByEg.
SELECT distinct
pd.description
, convert(varchar(10),convert(datetime, pe.enc_timestamp, 103),103) enc_timestamp
, pd.icd9cm_code_id
, dsm.description
, pd.note
FROM ngkbm_dxcode_to_category_ ndc
join patient_diagnosis pd ON pd.icd9cm_code_id = ndc.txt_icd9cm_code_id
left join ngkbm_categry_to_specialty_ c2s ON ndc.disease_category = c2s.txt_disease_category
left join provider_mstr pm ON pm.specialty_code_1 = c2s.txt_Prov_specialty_code
left join diagnosis_status_mstr dsm ON dsm.status_id = pd.status_id
join patient_encounter pe on pe.enc_id = pd.enc_id
ORDER BY enc_timestamp desc
Thats the OP's original query as part of the screenshot attached.
The problem lies with the order of logical query processing where order by is done after the select and as the column alias was the same as the column it was picking the column alias instead for ordering by the conversion instead of picking the column and ordering by datetime, hence the need for the sub select so that it can determin which enc_timestamp to order by.
Doh!!!, I didnt see the first screen shot, in that case changing the Alias for the column and on the Order by should work.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply