Skip to main content
S
splitzer
Inspiring
July 22, 2007
Question

sort multiple columns by Date

  • July 22, 2007
  • 5 replies
  • 805 views
I have this query which sorts the results by date, but for some reason its not doing it properly.

Query:
<cfquery name="wall" datasource="wall">
SELECT *
FROM wall.wall
WHERE profileID = #URL.id# OR myID = #URL.id#
ORDER BY date DESC
</cfquery>

Table:
ID, profileID, myID, date
1 1 2 7/14/2007 1:11:00 PM
2 2 9 7/17/2007 1:12:00 PM
3 3 2 7/20/2007 9:13:00 PM
4 7 2 7/22/2007 12:14:00 PM

After i convert to list, than i output, it comes out like this: 9,7,3,1, keeping in mind that we have removed all the 2's.

It suppose to be coming like 7,3,9,1 (again removing all the 2's from the list. ). Myabe i'm missing something?

Thanks for any help!
Syed
    This topic has been closed for replies.

    5 replies

    S
    splitzerAuthor
    Inspiring
    July 24, 2007
    anything else i should try?
    S
    splitzerAuthor
    Inspiring
    July 23, 2007
    Dan, here you go (cfdump result):
    DATE ID MYID PROFILEID
    1 2007-07-22 12:14:00.0 4 2 7
    2 2007-07-20 21:13:00.0 3 2 3
    3 2007-07-17 13:12:00.0 2 9 2
    4 2007-07-14 13:11:00.0 1 2 1

    Thats the cfdump output for the query. Also, what i'm trying to do is that sort the result of multiple columns, than remove the number "2" from the list. 2 is the the url.id.

    So, basically, sort multiple column result, create a list, remove url.id from that list, than display it.

    Its working fine, but the only problem is, while creating the list, it RE-SORT few numbers somehow.

    One more thing, the query is working fine, it sorts the result properly, as you can see cfdump results. The problem is in the creating list part.

    In case, if you need valueCombined results: 2,2,9,2,7,3,2,1
    Final_id results: 9,7,3,1


    What i need: 7,9,3,1 (WHY?, because 9 has a timestamp of: 7/17/2007 1:12:00 PM, which is second value, but it comes out to be first)

    I hope, this information helps.
    E
    efecto747
    Participating Frequently
    July 23, 2007
    Splitzer,

    There's a flaw in the way you're building your list - I'm not sure why you're doing it this way but you're appending one value list to another from the same query so there's no way your values will stay sorted once you do this.

    ie.
    You're taking the (sorted) value list of wall.myID:
    2
    2
    9
    2
    and then adding the (sorted) value list of wall.profileID to the end:
    7
    3
    2
    1
    The reason why its not sorted correctly is right there - 9 is appearing first because its in the first half of your list (see above).

    If you want the results to stay sorted you'll need to try a different method for combining the two lists of values, perhaps by looping through the query results and building the list row by row.




    S
    splitzerAuthor
    Inspiring
    July 23, 2007
    efecto747, the code you wrote did work. And, it much shorter to what i wrote. I was thinking of comparing the values of url.id with profileID using cfif, but i thought cfif wasnt going to accept multiple values, or search through multipel values to match it with url.id. Using under cfloop, is a nice idea.

    Thanks!
    D
    Dan_Bracuk
    Inspiring
    July 23, 2007
    Not sure what you are trying to accomplish, but why not start with simple things.

    What do you see when you cfdump your query?
    S
    splitzerAuthor
    Inspiring
    July 23, 2007
    cf_dev, thanks for pointing it out. The query is not the problem. Its because of the list i'm creating.

    Here is the list creation part:
    <cfset value1 = #ValueList(wall.myID,",")#>
    <cfset value2 = #ValueList(wall.profileID,",")#>
    <cfset valueCombined = value1 & "," & value2>

    <cfoutput>#valueCombined#</cfoutput>

    <cfset commaReplaceOne = "#URL.id#,">
    <cfset commaReplaceTwo = ",#URL.id#">

    <cfset new_replaced = #ReReplace(valueCombined, commaReplaceOne, "", "ALL")#>
    <cfset final_id = #ReReplace(new_replaced, commaReplaceTwo, "", "ALL")#>

    Notice how value1 and value2 are being generated by the query. Thats, when things going bad. It rearranges the numbers.
    C
    cf_dev2
    Inspiring
    July 23, 2007
    > it comes out like this: 9,7,3,1, keeping in mind that we have removed all the 2's.
    What does that mean?

    > After i convert to list
    Is the query really the problem or how you're creating your "list"?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices