CODEWITHSUNDEEP
mysqlsql
Tejas Gajjar
Tejas Gajjar

Reputation: 248

ordering in sql result

I have custom query to get data from DB. I am having a problem with ordering.

Here is what I am using to get data.

SELECT a.*, b.* FROM wp_terms AS a INNER JOIN wp_term_taxonomy AS b ON a.term_id = b.term_id
            WHERE
                b.taxonomy = "ticker" AND(
                    a.name LIKE "aa%" OR b.description LIKE "aa%"
                )
            ORDER BY 
                CASE 
                    WHEN(
                            a.name LIKE "aa%" AND b.description LIKE "aa%"
                        ) THEN 1 
                    WHEN(
                            a.name LIKE "aa%" AND b.description NOT LIKE "aa%"
                        ) THEN 2 
                    ELSE 3
                    END,a.name ASC
            LIMIT 0, 10

The result is showing like that.

enter image description here

Show I need a result to be show like

AA

First then other results.

it is working with single character

SELECT a.*, b.* FROM wp_terms AS a INNER JOIN wp_term_taxonomy AS b ON a.term_id = b.term_id
            WHERE
                b.taxonomy = "ticker" AND(
                    a.name LIKE "v%" OR b.description LIKE "v%"
                )
            ORDER BY 
                CASE 
                    WHEN(
                            a.name LIKE "v%" AND b.description LIKE "v%"
                        ) THEN 1 
                    WHEN(
                            a.name LIKE "v%" AND b.description NOT LIKE "v%"
                        ) THEN 2 
                    ELSE 3
                    END,a.name ASC
            LIMIT 0, 10

enter image description here

Upvotes: 0

Views: 63

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

The best possible solution for alphabetic ordering to sort with length first and then with the values -

SELECT a.*,
  b.*
FROM wp_terms               AS a
INNER JOIN wp_term_taxonomy AS b
ON a.term_id     = b.term_id
WHERE b.taxonomy = "ticker"
AND( a.name LIKE "aa%"
OR b.description LIKE "aa%" )
ORDER BY
  CASE
    WHEN( a.name LIKE "aa%"
    AND b.description LIKE "aa%" )
    THEN 1
    WHEN( a.name LIKE "aa%"
    AND b.description NOT LIKE "aa%" )
    THEN 2
    ELSE 3
  END,
  LENGTH(a.name),
  a.name ASC LIMIT 0,
  10

Upvotes: 2

Muhammad Kamran
Muhammad Kamran

Reputation: 109

  SELECT a.*, b.* FROM wp_terms AS a INNER JOIN wp_term_taxonomy AS b ON a.term_id = 
  b.term_id
            WHERE
                b.taxonomy = "ticker" AND(
                    a.name LIKE "aa%" OR b.description LIKE "aa%"
                )
            AND(
                CASE 
                    WHEN(
                            a.name LIKE "aa%" AND b.description LIKE "aa%"
                        ) THEN 1 
                    WHEN(
                            a.name LIKE "aa%" AND b.description NOT LIKE "aa%"
                        ) THEN 2 
                    ELSE 3
                    END)
            order by a.name ASC
            LIMIT 0, 10

Upvotes: 1

Related Questions