SitePoint Sponsor

User Tag List

Results 1 to 23 of 23

Hybrid View

  1. #1
    PHP Programmer QReyes's Avatar
    Join Date
    Oct 2001
    Location
    Philippines
    Posts
    698
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL: Proper sorting of a column

    -------------
    Title column
    -------------
    SitePoint
    1-2-3 Website
    The Matrix
    'San Pedro

    What I would like to do is to sort the Title column correctly ignoring the symbols, article, and have the number "1" be sorted as "one." If properly sorted, it should result to this:

    -------------
    Title column
    -------------
    The Matrix (Matrix, The)
    1-2-3 Website (One-Two-Three Website)
    'San Pedro (San Pedro)
    SitePoint (SitePoint)

    Is this possible with MySQL? If it is not, how can I solve this sorting issue without changing the name?
    Last edited by QReyes; Apr 28, 2005 at 23:46. Reason: Added the question mark icon.
    The Star Circle Quest Community - Community website for SCQ fans.
    Telebisyon.net - Reference guide to TV series shown in the Philippines.
    Lyrics Server Online! - All the lyrics you can get on one website.

  2. #2
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Sutton, Surrey
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, this is not possible in MySQL. It is not even possible in ANY database management system.

    The only possible solution, one that I first came across decades ago, is to have a totally separate "sort by" column which contains the data you want to use for sorting purposes. This would contain the values you have enclosed in parentheses in your example.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    order by
    case 
    when substring(title from 1 for 4) = 'The ' then substring(title from 5)
    when substring(title from 1 for 2) = 'A ' then substring(title from 3)
    when substring(title from 1 for 1) = '1' then Concat('One',substring(title from 2))
    end

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie
    Code:
     order by
     case 
     when substring(title from 1 for 4) = 'The ' then substring(title from 5)
     when substring(title from 1 for 2) = 'A ' then substring(title from 3)
     when substring(title from 1 for 1) = '1' then Concat('One',substring(title from 2))
     end
    that is sexy.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i've got some code similar to swampboogie's which will also concatenate the snipped part onto the end after a comma

    if you need it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    PHP Programmer QReyes's Avatar
    Join Date
    Oct 2001
    Location
    Philippines
    Posts
    698
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i've got some code similar to swampboogie's which will also concatenate the snipped part onto the end after a comma

    if you need it
    what is it? will it work for v4.0.22?
    The Star Circle Quest Community - Community website for SCQ fans.
    Telebisyon.net - Reference guide to TV series shown in the Philippines.
    Lyrics Server Online! - All the lyrics you can get on one website.

  7. #7
    PHP Programmer QReyes's Avatar
    Join Date
    Oct 2001
    Location
    Philippines
    Posts
    698
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    swampBoogie, can I use the code above for version 4.0.22 of MySQL?
    The Star Circle Quest Community - Community website for SCQ fans.
    Telebisyon.net - Reference guide to TV series shown in the Philippines.
    Lyrics Server Online! - All the lyrics you can get on one website.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select title   /* title is the original title */
         , case when substring_index(title,' ',1)
                      in ('a','an','the')
                then concat(
                        substring(title,instr(title,' ')+1)
                       ,', '
                       ,substring_index(title,' ',1)
                           )
                else title
             end as title2   /* title2 is what you sort on */
      from striparticles
    order by title2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if I wanted to add a WHERE clause using the newly created title2?

    Like if I only wanted every title starting with 'a'

    Or, if possible, what if I wanted a-m and n-z?

    I was doing something like this:
    WHERE left(title2,1)='a'

    but it wasn't working out.

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    push the query down into a subquery, so that you can the use the WHERE clause in the outer query
    Code:
    select *  
      from (
           select title   /* title is the original title */
                , case when substring_index(title,' ',1)
                             in ('a','an','the')
                       then concat(
                               substring(title,instr(title,' ')+1)
                              ,', '
                              ,substring_index(title,' ',1)
                                  )
                       else title
                    end as title2   /* title2 is what you sort on */
             from yourtable
          ) as reformatted
     where title2 like 'a%' 
    order 
        by title2
    by the way, this is one of the few times it is okay to use "select star" -- because the columns are defined right there in the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have this now :

    PHP Code:
    $selectt mysql_query("select *  
      from (
           select film_title
           , case when substring_index(film_title,' ',1)
                             in ('a','an','the')
                       then concat(
                               substring(film_title,instr(film_title,' ')+1)
                              ,', '
                              ,substring_index(film_title,' ',1)
                                  )
                       else film_title
                    end as title2  
             from films
          ) as reformatted
     where title2 like 'a%' 
    order 
        by title2"
    ,$dbh)
    or die(
    mysql_error()); 
    and am getting a mysql error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select film_title , case when substring_index(film_title,'


    Think I'm missing something

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by casbboy View Post
    Think I'm missing something
    yup, you missed the sticky thread which asks that if you're on a really old version of mysql, please let us know every time you post a question, so that we won't take time to develop a solution that you can't implement anyway

    could you please upgrade

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My bad, I forgot this post wasn't on my original thread where I had told that I'm still rocking 4.1.

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  14. #14
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have been delayed on upgrading cause I have 7 sites on that same server. Who knows if they'd all survive the upgrade (horror stories coming to mind).

    Any alternate solution?

    Ryan

    *it wouldn't be such a big deal if the same sites didn't represent ALL my income.
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you would not get that error in 4.1, you must be on 3.23 or 4.0
    Code:
    select title   /* title is the original title */
         , case when substring_index(title,' ',1)
                      in ('a','an','the')
                then concat(
                        substring(title,instr(title,' ')+1)
                       ,', '
                       ,substring_index(title,' ',1)
                           )
                else title
             end as title2   /* title2 is what you sort on */
      from striparticles
     where case when substring_index(title,' ',1)
                      in ('a','an','the')
                then concat(
                        substring(title,instr(title,' ')+1)
                       ,', '
                       ,substring_index(title,' ',1)
                           )
                else title
             end                like 'a%'
    order 
        by title2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm losing my mind. You're right, I'm rocking 4.0

    Ryan

    *I'll evaluate upgrading again today. But soooo scared.
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  17. #17
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works great by the way.

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  18. #18
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I've taken what was discussed here, made it a two-table query, and modified it to allow for a group by to choose the Most Viewed films first, and then each one's most viewed video. Here is what I have. I think I may be using the wrong JOIN, and I am still using the mysql 4.0 version (though I'm now running 5.x), so if there is a way to make this more efficient I'd be greatly appreciative:

    PHP Code:
    SELECT films.film_id 
         
    films.film_title
         
    films.film_keyw
         
    trailermobile.trailer_keyw
         
    , case when substring_index(films.film_title,' ',1)
                      
    in ('a','an','the')
                
    then concat(
                        
    substring(films.film_title,instr(films.film_title,' ')+1)
                       ,
    ', '
                       
    ,substring_index(films.film_title,' ',1)
                           )
                else 
    films.film_title
             end 
    as title2
      FROM films
    LEFT OUTER
      JOIN 
    SELECT trailers.trailer_id 
                  
    trailers.film_id
                  
    trailers.trailer_keyw
               FROM trailers
               WHERE trailers
    .trailer_title like '%Trailer'
               
    ORDER BY trailers.views DESC
           
    ) AS trailermobile
        ON trailermobile
    .film_id films.film_id
        where 
    case when substring_index(films.film_title,' ',1)
                      
    in ('a','an','the')
                
    then concat(
                        
    substring(films.film_title,instr(films.film_title,' ')+1)
                       ,
    ', '
                       
    ,substring_index(films.film_title,' ',1)
                           )
                else 
    films.film_title
             end like 
    '$char%'
             
    GROUP BY films.film_id
        order by films
    .views DESC LIMIT 3 
    This way works, but I feel it isn't responding as quick as my query I had before doing two columns. All suggestions/feedback appreciated.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, there are a couple of inefficiencies

    first, the subquery to get trailers has an ORDER BY which is useless (i'm not sure if mysql isn't smart enough to completely disregard it)

    second, the GROUP BY is useless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay,

    I removed the Order By and the query stopped working properly. Instead of giving me the top viewed video for each film, each just gave me any video for each film that matched '%Trailer'.

    I put the Order By back and it started working again.

    I removed the Group By and the query stopped working properly also. Instead of giving me the top three films with the top video for each one, it gave me the same film three times, with its top three videos.

    So, if my top films are Transformers, Taken and Twilight and they have videos like the following

    (films table) Transformers - 240k views
    (trailers table)
    Teaser Trailer - 82K views
    Feature Trailer - 130k views
    Trailer - 42k views

    Taken - 180k views
    Teaser Trailer - 140k views
    Trailer B - 98k views
    Feature Trailer - 70k views

    Twilight - 220k views
    Teaser Trailer - 80k views
    International Trailer - 60k views
    Trailer - 190k views

    The trailers have the film_id of the film they relate too. When the query runs, I want the Limit of 3 to return:


    Transformers - Feature Trailer
    Twilight - Trailer
    Taken - Teaser Trailer

    So the top three films by views, with each one showing their top viewed video.

    Cheers
    Ryan

    *oh, just for example, if I get rid of Group By I get as my result:

    Transformers - Feature Trailer
    Transformers - Teaser Trailer
    Transformers - Trailer
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i say again, the subquery ORDER BY and main query GROUP BY are useless -- they may appear to work, but they are simply masking an underlying inefficiency
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay,

    So basically there is a better way of writing for the same output the Order By and Group By are giving me?

    I've tried a few different queries without the group by and am at a loss. This was the only one that outputted exactly what I wanted.

    I'm thinking about just running a query grabbing top three films. And then, while looping that in PHP, just running individual queries to grab the top video for each film. so, in essence four queries. More efficient?

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  23. #23
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Way more efficient.

    I just ran the query with the films, and then looped 3 separate queries in.

    To run the query, the loop, and the other three queries it took 1 millisecond.

    The single query I had posted above took 29 milliseconds. A bit more coding now, but works great.

    I even added a function to check trailer existence in the looped queries, so I can load a backup video (like a clip) if one does not exist. Still only 1 millisecond to complete the job.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •