CODEWITHSUNDEEP
mysql
Akhil K RBz
Akhil K RBz

Reputation: 1

mysql query to select distinct rows from table when values in a column starts with same string in php

i want to get the distinct rows from MySQL table , with the table having a column with values having same starting string

mysql,php,apache

SELECT DISTINCT advt_id FROM `advtspacebkg`

in table advtspacebkg column advt_id contains values like 

'KNR28629/1','KNR28629/2','KNR28629/3',.....

how can i get the rows having advt_id starting with 'KNR28629'

->thanks in advance

Upvotes: 0

Views: 639

Answers (2)

CreativeMinds
CreativeMinds

Reputation: 343

This is solve your problem.

Query

SELECT DISTINCT LEFT(`advt_id`,LOCATE('/',`advt_id`) - 1)  FROM `advtspacebkg`

Input

advt_id
__________
KNR28629/1
KNR28629/2
KNR28629/3
KNR28630/1
KNR28630/2
KNR28630/3

Output

advt_id
________
KNR28629
KNR28630

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Use LIKE:

SELECT DISTINCT advt_id
FROM advtspacebkg
WHERE advt_id LIKE 'KNR28629%';

Your query could also benefit from the following index on advt_id:

CREATE INDEX idx ON advtspacebkg (advt_id);

Upvotes: 2

Related Questions