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!
MySQL Server

Avatar of undefined
Last Comment
JJ123

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
akshah123

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
akshah123

Damn u angelIII, u beat me by seconds and with a better explaination.  (Insert angry fist waving here) .  ;)

JJ123

ASKER
Thank you angelIII - that worked perfect. I actually need to query all of the other fields so your solution works the best.
Your help has saved me hundreds of hours of internet surfing.
fblack61