Sql Select Distinct Substring Where Like Muddleup Howto

SQL select distinct substring where like muddleup howto

This will work for SQL Server. If you use something else you need to figure out the corresponding functions to left and charindex. Left could of course be replaced with a substring.

select distinct left(T.Animal, charindex(' ', T.Animal, 1)-1)
from YourTable as T

Result:

-------------------------
BIRD
CAT
DOG
FISH

In MySQL you would use left and locate.
(Code not tested)

select distinct left(T.Animal, locate(' ', T.Animal, 1)-1)
from YourTable as T

How to return distinct domain names from email address values in MySQL?

Just use group by

SELECT SUBSTRING_INDEX(user_email,'@',-1) as domain_name FROM user_email group by domain_name

How to select the first row for each group in MySQL?

rtribaldos mentioned that in younger database versions, window-functions could be used.

Here is a code which worked for me and was as fast as Martin Zwarík's substring_index-solution (in Mariadb 10.5.16):

SELECT group_col, order_col FROM (
  SELECT group_col, order_col
  , ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY order_col) rnr 
  FROM some_table
  WHERE <some_condition>
) i
WHERE rnr=1;

---

Related Topics