CODEWITHSUNDEEP
sql
Ankit
Ankit

Reputation: 2246

IN Clause in SQL Query

I had one SQL Query: 

SELECT NAME
FROM CATEGORY_LANGUAGE 
WHERE 
CATEGORY_ID IN (11, 22)
AND LANGUAGE_ID=1

IN THIS QUERY:

11 -> aa

22 -> bb

I want records to be returned like this:

aa

bb

While running the query returns:

bb

aa

Kindly, let me know to further elaborate my question.

Upvotes: 1

Views: 549

Answers (4)

Frank Schmitt
Frank Schmitt

Reputation: 30765

If you always want to sort by category_id:

SELECT NAME
FROM CATEGORY_LANGUAGE 
WHERE 
CATEGORY_ID IN (11, 22)
AND LANGUAGE_ID=1
ORDER BY CATEGORY_ID

If, on the other hand, you always want to have the same order as in your IN-Clause, I'd use some kind of INSTR() magic (depends on your RDBMS, this works for Oracle; I put the hyphens in for readability and to avoid possible errors if one category_id is a prefix of another):

SELECT NAME
FROM CATEGORY_LANGUAGE 
WHERE 
CATEGORY_ID IN (11, 22)
AND LANGUAGE_ID=1
ORDER BY INSTR('11-22', TO_CHAR(CATEGORY_ID));

Explanation: TO_CHAR() converts CATEGORY_ID to a string, and INSTR() returns the index where the second string was found in the first - here, we use it to extract the sort order for our category_id from '11-22'.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

The SQL code

CATEGORY_ID IN (11, 22)

is merely syntactic sugar for 

(CATEGORY_ID = 11 OR CATEGORY_ID = 22)

The SQL engien is free to evaluate these predicates in any order is sees fit (hint: OR is associative).

It may look like a set, relation, table, tuple, list, bag etc but isn't.

SQL only really has one data structure: the table. Therefore, put your values into rows of a staging table (could be a CTE). A table doesn't have any intrinsic ordering so if this is a requirement you will need to give your stating a sort order attribute. You can then JOIN the staging table to your target base table.

Here's some Standard SQL as an example:

WITH STAGING (CATEGORY_ID, SORT_ORDER)
     AS
     (
      SELECT CATEGORY_ID, SORT_ORDER
        FROM (
              VALUES (11, 0), 
                     (22, 1)
             ) AS STAGING (CATEGORY_ID, SORT_ORDER)
     )
SELECT C1.NAME, S1.SORT_ORDER
  FROM CATEGORY_LANGUAGE AS C1
       INNER JOIN STAGING AS S1
          ON C1.CATEGORY_ID = S1.CATEGORY_ID 
 WHERE LANGUAGE_ID = 1
 ORDER 
    BY SORT_ORDER;

Upvotes: 1

Andrew Jackman
Andrew Jackman

Reputation: 13966

Here is something new:

SELECT NAME
FROM CATEGORY_LANGUAGE 
WHERE 
    CATEGORY_ID IN (11, 22)
    AND LANGUAGE_ID=1
ORDER BY
    field(CATEGORY_ID, 11, 22)

This is catered to mysql. Just make sure the ids are in the same order in the field() function as they are in the in() function.

Upvotes: 2

Ibu
Ibu

Reputation: 43810

SELECT 
  NAME 
FROM 
  CATEGORY_LANGUAGE  
WHERE  
  CATEGORY_ID IN (11, 22) 
AND 
  LANGUAGE_ID=1 
ORDER BY NAME ASC

Maybe it is just an order problem

Upvotes: 0

Related Questions