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

READ ALSO
Mysql Optimization suggestion for large table

Mysql Optimization suggestion for large table

i want to optimize this query,

122
Script for tacking mysql backup in ubuntu 16

Script for tacking mysql backup in ubuntu 16

Plz help me to write the script in ubuntu for tacking mysql backup,I have already written few line in the scriptsh file for tacking mysql dump and run this script from crontab, but it is not working

213
Google CHARTS Display 0 values. (MySQL data with datetime)

Google CHARTS Display 0 values. (MySQL data with datetime)

I have been struggling with google charts with MySQL data

273
SQL Query to select data based on year and month

SQL Query to select data based on year and month

I'm writing an ecommerce app and im writing a script to track the sales of items according to year/month

127