MySQL Select the field as Distinct having data type as Text with filtering
129
December 26, 2017, at 7:04 PM
If we have a table T1 with a field named orders have data type as Text.
Such as the following:
user:1|number:2|expiry:9/2018
user:3|number:2|expiry:9/2018
user:1|number:3|expiry:1/2020
And i would like to SELECT DISTINCT based on number: and expiry:
So expected output gonna be:
user:1|number:2|expiry:9/2018
user:1|number:3|expiry:1/2020
How to achieve that ?
Answer 1
You should consider to restructure your data and save these details in separate columns or in another table and in different columns to avoid such structural issues.
For now with existing structure you could use aggregation to get your expected data
select min(orders) orders
from table1
group by substring_index(orders,'|',-2)
http://rextester.com/GLT33046
POPULAR ONLINE
- search for elements in a list 105333 visits
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 97548 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 93633 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 91034 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 87834 visits
- NetBeans IDE - ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver 56320 visits
- Adding methods to es6 child class 20088 visits
