troubleshooting Question

DISTINCT and LIMIT is not working in query

Avatar of JJ123
JJ123 asked on
MySQL Server
4 Comments1 Solution971 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros