Geek asks and answers

Is there a more efficient way to make this SQL choice?

I have this SQL query:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY CASE IMAGENAME
  WHEN 'IMG1' THEN 1
  WHEN 'IMG2' THEN 2
  WHEN 'IMG3' THEN 3
  WHEN 'IMG4' THEN 4
  WHEN 'IMG5' THEN 5
  WHEN 'IMG6' THEN 6
  ELSE 7
END

I can’t guarantee that the IMAGENAME list will be in alphabetical order, so the case statement, but I would prefer to sort in the database and not in the code, because I trust their sort code better than mine :)

The SQL server analyzes that 78% of the runtime is spent sorting - can I reduce this?

It should be pretty vanilla SQL, as we focus on SQL Server and Oracle.

Any tuning advice would be fantastic.

+3
source share
9 answers

, , , , , , . MySQL , , FIELD():

SELECT * FROM IMAGES WHERE
  IMAGENAME IN ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
  ORDER BY FIELD(IMAGENAME, 'IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6');

FIELD() , , , , . .

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

, SQL Server Oracle.

+6
SELECT *
FROM IMAGES
WHERE IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENAME ASC

; , , . , "else" , , where.

: , , .

+2

IMG ( ) IMAGENAME , ?

:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENO
END

IMAGENO 1, 2, 3, 4, 5, 6 .

0

CAST(SUBSTRING(IMAGENAME, 4, LEN(IMAGENAME) -3) as INTEGER)

, , Oracle - , ,

, - Rank , ,

0

"IMG", char 4, . SQL Server, Oracle :

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY TO_NUMBER(SUBSTR(IMAGENAME,4));
0

, , , , , , .

, .

0

, ChrisThomas123 SQL Server ( ORACLE), :

ORDER BY CHARINDEX(IMAGENAME, 'A,B,C,D,E,F,G')

, , .

0

, ( sql), IMAGENAME, . , , , IMAGENAME, . numecic, . .

0

,

(IMAGENAME, SEQUENCENUMBER).

('IMG1', 1), ('IMG2', 2), ('IMG3', 3),... SEQUENCENUMBER.

.

0

More articles:


All Articles