Link to home
Start Free TrialLog in
Avatar of JJ123
JJ123

asked on

DISTINCT and LIMIT is not working in query

I have a query where I am parsing the field name to display in PHP. I am trying to get the last 10 entries to display. The data that is in the field file_name is similar to 1_SI_777777.xls, 2_SI_777777.xls, 1_SI_888888.xls. So I parsed the #_SI_ off of the field. So then there are some values that are equal. I just want the distinct values to be displayed. What am I doing wrong?

select distinct(substring(SUBSTRING(file_name,LOCATE('_',file_name)+2),LOCATE('_',substring(file_name,LOCATE('_',file_name)+1)))) as file, date_created, status from tblfiles order by date_created desc limit 10

this is what is displayed for file:
777777.xls
777777.xls
777777.xls
999999.xls
10.xls
11.xls
12.xls
12345.xls
123455.xls
123456.xls

So I don't want 777777.xls to be listed three times - just once.

Any help is appreciated.

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try just this ...

select distinct substring(SUBSTRING(file_name,LOCATE('_',file_name)+2),LOCATE('_',substring(file_name,LOCATE('_',file_name)+1))) as file
from tblfiles order by date_created desc limit 10
Damn u angelIII, u beat me by seconds and with a better explaination.  (Insert angry fist waving here) .  ;)

Avatar of JJ123
JJ123

ASKER

Thank you angelIII - that worked perfect. I actually need to query all of the other fields so your solution works the best.