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.

ee_rlee

hi, try this

This should return records with Height='Edge' as the 1st record, then sort asc.

select distinct CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))), Height 
from TEST_DATA where series = 'ABC' 
ORDER BY CASE WHEN Height<>'Edge' THEN CONVERT(real, RTRIM(REPLACE(Height, 'mm', ''))) END ASC

Open in new window

sl1nger

ASKER

I get this error??

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SOLUTION

ee_rlee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

orcic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sl1nger

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

ee_rlee

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

Open in new window