MySQL: Apply ORDER BY on a field having datatype varchar

Question

I have a db table like:

----------------------------
id  name        cat
============================
1   20.gif      logo
2   21.gif      logo
3   22.gif      logo
4   15.gif      web
5   16.gif      web
6   17.gif      web
7   23.gif      logo
8   18.gif      web
9   19.gif      web

and I want to fetch highest (latest inserted record for each cat) some thing like:

----------------------------
id  name        cat
============================
7   23.gif      logo
9   19.gif      web

for this I queried:

SELECT id, name, cat FROM portfolio GROUP BY cat ORDER BY name DESC

but because name field is an string so I cant apply ORDER BY on name.

any idea..?

I thing I have to use some REGEXP in my query but I don't know how :( — PHP Ferrari, May 17 '12 at 05:40
you must be doing something wrong. `order by` works for all.. try looking for some else error — Addicted, May 17 '12 at 05:43

score 1 · Answer 1 · answered May 17 '12 at 05:46

1

SELECT id, name, cat 
FROM portfolio 
GROUP BY cat 
ORDER BY cast(substring(name, 1, INSTR(name, '.') - 1) as signed) DESC

SQLFiddle example

answered May 17 '12 at 05:46

juergen d

201,996
37
293
362

1

The `ORDER BY` doesn't perform sorting until after the `group by` is applied. – Ja͢ck May 17 '12 at 06:25

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

Yes I found the solution :

select id, name, cat from (select * from portfolio ORDER BY id DESC) AS x GROUP BY cat

I got help from Retrieving the last record in each group

Thanks to Stackoverflow which really make developers life easy :)

edited May 23 '17 at 12:27

Community

1
1

answered May 17 '12 at 05:53

PHP Ferrari

15,754
27
83
149

just don't be surprised that implicit aggregation function "first record in a group" won't work in other RDBMS when using `group by`... – Aprillion May 17 '12 at 06:31
You are not ordering by name, but by id ;) – Maxim Krizhanovsky May 17 '12 at 06:54

MySQL: Apply ORDER BY on a field having datatype varchar

2 Answers2