asked on
Query distinct on numeric and character
I currently have a query that does a distinct, so I can order the values on the screen.
select distinct CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))), Height from TEST_DATA where series = 'ABC' ORDER BY CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))) ASC
It works fine, but problem now... I don't have all numeric values in the 'xxmm' format in my Height records.
Now I have 2 records with 'Edge' in the record. So, I need to find a new way to order the above query with these new records.
select distinct CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))), Height from TEST_DATA where series = 'ABC' ORDER BY CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))) ASC
It works fine, but problem now... I don't have all numeric values in the 'xxmm' format in my Height records.
Now I have 2 records with 'Edge' in the record. So, I need to find a new way to order the above query with these new records.
Microsoft SQL Server
Last Comment
ee_rlee
ASKER
I get this error??
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
SOLUTION
THIS SOLUTION 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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION 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.
ASKER
ee rlee--
1 more thing... If the Edge exists, I need it to display as the first or last result w/ the numeric values
1 more thing... If the Edge exists, I need it to display as the first or last result w/ the numeric values
try
select CASE WHEN Height='Edge' THEN 'Edge' ELSE RTRIM(REPLACE(Height, 'mm', '')) END, Height
from TEST_DATA
where series = 'ABC'
group by Height
ORDER BY CASE WHEN Height='Edge' THEN 0 ELSE CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))) END ASC
This should return records with Height='Edge' as the 1st record, then sort asc.
Open in new window