Order by string name from database

Question

I stored string variables in a database, either 'Abstain', 'Block', 'No', or 'Yes'. Currently I am grabbing these using an ORDER BY position however, instead of alphabetic order, I would like to order them like: 'Block', 'No', 'Abstain', 'Yes'. Is there an easy way to do this in Rails?

My exact code for this is:

def self.unique_votes(motion)
  Vote.find_by_sql("SELECT * FROM votes a WHERE created_at = (SELECT MAX(created_at) as created_at FROM votes b WHERE a.user_id = b.user_id AND motion_id = #{motion.id}) ORDER BY a.position")
end

NB: I am using Postgres SQL and am looking for a solution that i can use in the select statement Thanks!

I haven't currently they are just strings in the database that is my question how can i define that order instead of alphabetically sorry if this was unclear! — Jesse Whitham, Sep 11 '12 at 04:51

Rikki · Accepted Answer · 2012-09-11T05:37:50.147

7

This is one of the ways to do that. By the way, the best way to do such a thing is to have another table named "Position" that identifies every value by a number and a foreign key in the "votes" table to reference the primary key of "Position" table. Also you can have another column named "Order" in the "Position" table to store those numbers and sort with. You should do these things in the insert time, not on every select.

Cheers

SELECT  *
,   Case    a.position
        When    'Block'     Then    0
        When    'No'        Then    1
        When    'Abstain'   Then    2
        When    'Yes'       Then    3
        Else    -1
    End                     As  Position_Value
    FROM    votes   a
    WHERE   created_at =    (
        SELECT  MAX(created_at) as  created_at
            FROM    votes   b
            WHERE   a.user_id   =   b.user_id
            AND motion_id   =   #{motion.id}
    )
    ORDER   BY  Position_Value

Any way if it does not work as it should! you can try this one:

Select  *
    From    (
        SELECT  *
    ,   Case    a.position
            When    'Block'     Then    0
            When    'No'        Then    1
            When    'Abstain'   Then    2
            When    'Yes'       Then    3
            Else    -1
        End                     As  Position_Value
        FROM    votes   a
        WHERE   created_at =    (
            SELECT  MAX(created_at) as  created_at
                FROM    votes   b
                WHERE   a.user_id   =   b.user_id
                AND motion_id   =   #{motion.id}
        )
    )   As  Result
    ORDER   BY  Position_Value

edited Sep 11 '12 at 05:37

answered Sep 11 '12 at 04:52

Rikki

3,338
1
22
34

Have tried this exact wording out in my application and it did not work is "As Position_Value" supposed to be out of the switch statement? This seems like the best way for me to do this. – Jesse Whitham Sep 11 '12 at 05:01
"it did not work" is not a complete reference for me to know what happened there. Could you please tell me what was the error? – Rikki Sep 11 '12 at 05:03
Sorry there was no error in the code side, but there seems to be no ordering applied. The statement i had was ordering it just the wrong way. – Jesse Whitham Sep 11 '12 at 05:05
What is the order? it should be in order from 0 (Block) to 3 (Yes). – Rikki Sep 11 '12 at 05:07
Seems random with my test page its goes: Yes, Block, Block, Abstain, Yes, Abstain, No. – Jesse Whitham Sep 11 '12 at 05:09
What version of SQL you are using sir? – Rikki Sep 11 '12 at 05:10
PostGres sorry should have stated that! – Jesse Whitham Sep 11 '12 at 05:12
@LearningDev What do you mean I should have stated that? That refers to what? – Rikki Sep 11 '12 at 05:15
I just mean i should have told everyone what database i was using in the question sorry. – Jesse Whitham Sep 11 '12 at 05:16
Ah, I got it. It's ok, just help me to solve the problem. What is the version? – Rikki Sep 11 '12 at 05:16
Execute this "Select @@Version" and paste the value here please. Thank you. – Rikki Sep 11 '12 at 05:18
Using Postgres 9.1 sorry i couldn't find anywhere to do the @@version but went to my Postgres on machine and its 9.1 – Jesse Whitham Sep 11 '12 at 05:26
It's because you are not using SQL Server. Try the second code I just updated in the answer see what happens. – Rikki Sep 11 '12 at 05:28
So turns out i was the one being the idiot your first code worked but my variables where different then what i said no capitals at front sorry for the hassle thank you so much =) – Jesse Whitham Sep 11 '12 at 05:35
2

You can put the CASE right into the ORDER BY if you don't need the position. – mu is too short Sep 11 '12 at 05:46
Thanks for that @mu is too short makes it quite nice and clean =) – Jesse Whitham Sep 11 '12 at 06:52

score 4 · Answer 2 · answered Sep 11 '12 at 04:53

4

Whenever I have a series I need to sort into an unnatural order, I add an additional integer field, and put values into it that will force the string values into whatever order I want them.

Instead of having only:

'Abstain'
'Block'
'No'
'Yes'

I'd have a second field:

'Abstain' 3
'Block'   1
'No'      2
'Yes'     4

Sorting on the second column allows me to retrieve the values in my desired order:

'Block'   1
'No'      2
'Abstain' 3
'Yes'     4

answered Sep 11 '12 at 04:53

the Tin Man

158,662
42
215
303

Thanks for that a very valid and useful suggestion however i really cant change the database schema (at this stage anyway) – Jesse Whitham Sep 11 '12 at 05:02
This is what I do in my applications as well. – No'am Newman Sep 11 '12 at 05:04
1

@LearningDev, Then you should put that criteria into your question. – the Tin Man Sep 11 '12 at 05:04
Sorry still new to this @the Tin Man will get the hang of it didn't mean to be ambiguous but this will definitely help me in the future thanks. – Jesse Whitham Sep 11 '12 at 05:10

score 2 · Answer 3 · answered Sep 11 '12 at 04:51

2

If this is a permanent requirement, you can store them in a different table with another column 'ordering' that carries the number to print them in desired order, join your results with this table and ORDER BY the ordering column.

If this is a temporary, you can join your results with UNION of bunch of SQL statements that select the value and the ordering and then order by the ordering column.

answered Sep 11 '12 at 04:51

Vikdor

23,934
10
61
84

So the suggestion is that I should create a temporary ordering table? This is not a permanent requirement and should only apply to the one part of a much bigger application, I have seen some stuff about an "ORDER BY" statement that can sort the strings out is this a possibility? – Jesse Whitham Sep 11 '12 at 04:57

score 0 · Answer 4 · edited May 23 '17 at 11:45

0

You could define something like MySQL's FIELD function, ie an index lookup into an array, then use that for your ORDER BY. It's a bit more concise than an array.

See: https://stackoverflow.com/a/12366184/398670

edited May 23 '17 at 11:45

Community

1
1

answered Sep 11 '12 at 09:10

Craig Ringer

307,061
76
688
778

Order by string name from database

4 Answers4