SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    [MySql 5.0.45-community-nt] Order by Case When and CHAR_LENGTH

    Hello there, I hope your help.

    I have problem with this query and your order.
    Code:
    SET @Name = 'D';
    
    SELECT
    	Name
          , Group
    
    FROM
    	tbl_Names
    WHERE
    	(
    		Group LIKE CONCAT('%' ,@Name, '%')
    		AND CHAR_LENGTH(Group)BETWEEN 1
    		AND 4
    	)
    ORDER BY
    	CASE
    WHEN CHAR_LENGTH(@Name)= 1 THEN
    	1
    ELSE
    	0
    END;
    Query OK, 0 rows affected
    +-----------------------+------------+
    | Name                  | Group      |
    +-----------------------+------------+
    | DANIEL T              | D100       |
    | DAVID  L              | D200       |
    | DENISE C              | D300       |
    | DONALD S              | D300       |
    | IR FIRST GROUP        | D          |
    +-----------------------+------------+
    5 rows in set
    I need this other output when CHAR_LENGTH(@Name)= 1.
    Can you help me?
    Code:
    +-----------------------+------------+
    | Name                  | Group      |
    +-----------------------+------------+
    | IR FIRST GROUP        | D          |
    | DANIEL T              | D100       |
    | DAVID  L              | D200       |
    | DENISE C              | D300       |
    | DONALD S              | D300       |
    +-----------------------+------------+

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change this --
    Code:
    ORDER BY
    	CASE
    WHEN CHAR_LENGTH(@Name)= 1 THEN
    	1
    ELSE
    	0
    END;
    to this --
    Code:
    ORDER BY
    	CASE
    WHEN CHAR_LENGTH(@Name)= 1 THEN
    	0
    ELSE
    	1
    END
    , Group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks alot!


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
  •