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(SUBSTRI NG(file_na me,LOCATE( '_',file_n ame)+2),LO CATE('_',s ubstring(f ile_name,L OCATE('_', 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!
select distinct(substring(SUBSTRI
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Damn u angelIII, u beat me by seconds and with a better explaination. (Insert angry fist waving here) . ;)
ASKER
Thank you angelIII - that worked perfect. I actually need to query all of the other fields so your solution works the best.
select distinct substring(SUBSTRING(file_n
from tblfiles order by date_created desc limit 10