How to SELECT by first characteres and dont get duplicated row? SQL-CodePudding

I am bringing data from some courses, which have some sections like "A", "B"... I would like to get only the main course without a section. Image shows the main course at the end and other sections above. Using that course I want to get only 1 / 4 / C-501 / Matematicas Aplicadas I / C-501 .

This is my query. I tried using DISTINC LEFT(mc.shortname,5) and substr(mc.shortname,1,5) but it doesn´t work. Just cut the section but still bringing all their information.

SELECT mc.id, (SELECT mcd.value from mdl_customfield_data mcd WHERE mcd.fieldid =5 AND mc.id = mcd.instanceid)tipo_curso, mcd.value creditos, mc.fullname, mc.shortname
FROM mdl_course mc,mdl_customfield_data mcd
WHERE mc.category = 10
AND mc.id = mcd.instanceid
AND mcd.fieldid = 1;

CodePudding user response：

Well, if the rows where shortname always ends in a single letter are the rows you do not want returned.

You can use REGEXP to pattern match that in your WHERE clause.

SELECT
   *
from courses
where right(lower(shortname),1) not regexp '[a-z]'

Fiddle here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2e9b8195b5c93dc1551b25aae6d50578

CodePudding user response：

SELECT DISTINCT ON(shortname) * from table ORDER BY shortname;