Mysql – Order By Case String Not Working

casegroup byMySQL

I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I would expect:

MAT  - Mattel
MFST - Microsoft
MA   - MasterCard
MDLZ - Mondelez
MON  - Monsanto

It queries the "ticker" just fine, but how would I get it to allow both? If I search MO instead of "M", I would expect to see this:

MON  - Monsanto
MDLZ - Mondelez

I want it to give bias towards the "ticker", but also query the name if it is like the search term. Here is my code:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName` 
FROM `stocks` 
WHERE `stock_ticker` = 'M' 
GROUP BY `stock_ticker`, `stock_simpleName` 
ORDER BY CASE  
    WHEN `stock_ticker` LIKE 'M%' THEN 1 
    WHEN `stock_simpleName` LIKE 'M%' THEN 2 
    ELSE 3 
END 
LIMIT 5

I wrote a different query that easily pulled the "ticker" alone, but trying to query them both with bias towards one of the columns like stated above seems to be a bit difficult. My AJAX returns an error which obviously means it's returning a PHP error rather than the data that should be requested.

Is there a tweak for this to get it to work?

Best Answer

So I tweaked the code a bit by testing it in phpMyAdmin, and this is what worked:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName` 
FROM `stocks` 
GROUP BY `stock_ticker`, `stock_simpleName` 
ORDER BY CASE 
    WHEN `stock_ticker` LIKE 'MO%' THEN 1 
    WHEN `stock_simpleName` LIKE 'MO%' THEN 2 
    ELSE 3 
END 
LIMIT 5

I just removed the WHERE Statement entirely. It works exactly like I needed it to. I thought WHERE was always required for any statement that succeeds the FROM table.column part of the query. `

Related Solutions

SQL Server – Why Wildcards in GROUP BY Statements Do Not Work

GROUP BY A.* is not allowed in SQL.

You can bypass this by using a subquery where you group by, and then join:

SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN 
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2 
        GROUP BY FKey
      ) AS B 
    ON A.PKey = B.FKey ;

There is a feature in SQL-2003 standard to allow in the SELECT list, columns that are not in the GROUP BY list, as long as they are functionally dependent on them. If that feature had been implemented in SQL-Server, your query could have been written as:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

Unfortunately, this feature has not yet been implemented, not even in SQL-Server 2012 version - and not in any other DBMS as far as I know. Except for MySQL which has it but inadequately (inadequately as: the above query will work but the engine will do no checking for functional dependency and other ill-written queries will show wrong, semi-random results).

As @Mark Byers informed us in a comment, PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation.

Mysql – alternative to trigger. Cascade

I'm doing a fair amount of guessing here, but this is how I imagine that your model should look like. In a couple of tables I removed id columns to make it clearer, you may want to put them back in case the keys I have chosen is not stable enough:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
);

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock have 1 user 
create table user_stock (
    user_id int(10) unsigned NOT NULL,
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade,
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
);

insert into user_stock(stock_code, value, user_id) 
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)

Best Answer

Related Solutions

SQL Server – Why Wildcards in GROUP BY Statements Do Not Work

Mysql – alternative to trigger. Cascade

Related Question