Quick MySQL ORDER BY question

[alocurto]

I have a field. It has a value called 'REG' and then a bunch of other random values. What I would like to do is when I order the results from a query I would like to have REG show up first (or last depending on the ASC/DESC) ex:

values in DB:
test1
REG
REG
REG
test2
test3
REG
test4
atest5

Sorted the way I want (ASC):
REG
REG
REG
REG
atest5
test1
test2
test3
test4

and the reverse for DESC. Is there a simple way to do this?

 

[Mitzi]

SELECT column1 a , column2 b
FROM table_name
ORDER BY column1 a.ASC, b.DESC

The above will order column1 ascending and column2 descending. You should be able to modify easily as required.

Cheers

 

[alocurto]

Maybe I didn't describe it right, I am not sure how that will help solve the problem.

I have a table (myTable) and a column (myColumn). myColumn has values:

test1
REG
REG
REG
test2
test3
REG
test4
atest5

now, I want a SELECT * FROM myTable <insert MySQL I don't know> to output:

REG
REG
REG
REG
atest5
test1
test2
test3
test4

Notice this about the results. atest5 is AFTER REG and all the REG values are grouped together. Does that help out any?

Mitzi- I don't see how you are getting to coulmns and what you did there. Can you elaborate?

 

[Mitzi]

Mitzi- I don't see how you are getting to coulmns and what you did there. Can you elaborate?

SELECT column1 a , column2 b
FROM table_name
ORDER BY column1 a.ASC, b.DESC

What I've done there is give column 1 an alias of 'a' and column2 an alias of 'b' that way I can refer to the columns in the ORDER by clause by a simple name. So can I can say order column 'a' in ascending order and column 'b' in decending order.

An ORDER by clause will only order alphabetically either ascending or decending so you can't have something beginning with 'R', then 'A' then 't'.

The statement:

SELECT * FROM myTable ORDER BY myColumn

Is possibly the closest you will be able to get, though the list will be sorted alphabetically.

Sorry I can't be much more help.

 

[b4u]

Hi again alocurto

Let me show a simple way of achieving what you want:

Imagine the following table:


TableName: tblTestTable
Columns: ID, Info
Values:
ID Info
1 d
2 c
3 z
4 REG
5 x


So a normal order by would be:

SELECT ID, Info
FROM tblTestTable TT
ORDER BY Info ASC

Result:
ID Info
2 c
1 d
4 REG
5 x
3 z

I know that's the result you don't want. To get what you want you can use:

SELECT ID, Info
FROM tblTestTable TT
ORDER BY FIND_IN_SET('REG',Info) DESC, Info ASC

Your Result:
ID Info
4 REG <-- First position, followed by normally ordered results
2 c
1 d
5 x
3 z


Edit:
Ops, I forgot this part ... what does FIND_IN_SET do?

Well, it's simple ... FIND_IN_SET('REG',Info) will find the string 'REG' in the field Info. If it finds, returns 1, if not, returns 0. Since 1 is greater than 0, if you order it DESC, you'll put it first ...
End of Edit


Hope that fits your needs (if I correctly understood your question).

Cya.

 

[joshg]

SELECT ID, Info
FROM tblTestTable TT
ORDER BY FIND_IN_SET('REG',Info) DESC, Info ASC

I think this is what he is wanting to do. Also, if you wanted to sort it the opposite way, you'd have it as:

SELECT ID, Info
FROM tblTestTable TT
ORDER BY FIND_IN_SET('REG',Info) ASC, Info DESC

 

[alocurto]

Once again, you are the man!