Select distinct and order by

Being your typical lazy programmer, i needed to create a LMS script file for management.  It was to be two columns, pipe delimited, with a header.  No problem.  Do the query, copy the two columns, paste into excel, write a formula to concatenate the two columns with the pipe.  Run it

DRAT, forgot the header.

But wait – how about this:

Use DatabaseName
select course_id + “|” + batch_uid|
from course_main where course_id like ‘%10FA’

Even better

Select distinct cm.course_id + ‘|’+ cm.batch_uid as ‘COURSE_ID|EXTERNAL_COURSE_KEY’
from course_main cm
where cm.course_id like ‘%[_]10FA’

and we get great output (select all, copy with column header) already for running.  But the order of the courses was confusing, and i wanted to order them with “order by cm.course_id.  

I got an error message about needing ‘tthe column ordered by in the select criteria’ – (Humm what part of  “distinct cm.course_id” do you not understand?)  Maybe it is the distinct…

Nope – when i used an alias to get the header, i confused SQL – i needed to order by ‘COURSE_ID|EXTERNAL_COURSE_KEY’.  And besides, i only have one column as output – which is NOT named ‘course_id’ or even ‘distinct course_id’.

This entry was posted in Uncategorized. Bookmark the permalink.