I want to only display the highest value of them all, within a foreach loop. The following query is inside this foreach loop.
SELECT MAX(substring_index(data_column, ' ', -1))
FROM table
WHERE id = '{id-from-the-loop}'
AND data_column != ''
The query gives me this result:
Data A
Data A
Data C
Data B
Data G
Data E
G is the highest value in the list above, so it will show like the list below, but I can't figure it out!
Data
Data
Data
Data
Data G
Data
How can I solve this?
You can try this, write a subquery to get highest value of them all by limit 1 and order by
then self outer join with coalesce function.
CREATE TABLE T(col varchar(50));
INSERT INTO T VALUES ('Data A');
INSERT INTO T VALUES ('Data A');
INSERT INTO T VALUES ('Data C');
INSERT INTO T VALUES ('Data B');
INSERT INTO T VALUES ('Data G');
INSERT INTO T VALUES ('Data E');
Query 1:
SELECT coalesce(t2.col,substring_index(t1.col, ' ', 1)) col
FROM T t1 LEFT JOIN (
select col
from t
order by col desc
limit 1
) t2 on t1.col =t2.col
| col |
|--------|
| Data G |
| Data |
| Data |
| Data |
| Data |
| Data |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments