Værdi-ændring ved sortering

the solution will more than likeley depend on which database you are using
also, what if other values are higher than 100?

Du kan prøve med:

SELECT * FROM Tabel ORDER BY (Value=100) DESC, Value

Skulle selvfølgelig være:

SELECT * FROM Tabel ORDER BY (Value=0) DESC, Value

Access
SELECT *
FROM YourTable
ORDER BY IIf([value]=0,100,[value]);

>Terry: what happens if you try to access your query from outside Access (eg. from Microsoft Query in Excel) - Hint: is IIf() an Access-function like nz(), which CANT be used in MS Query.

I wouldnt know, but thats why I wrote (Access). I must admit thatI prefer your method, which will I assue work in Excel.

Yes - the boolean function (Value=0) is generic and the only difference from system to system is weather is has to be sorted ASC or DESC.

Terry: I'm using db2.

Other values higher than 100 should be placed after 0 when sorting. That is, if the tuple (kat, 120) gets inserted it should be placed last in the above example.

Proaccess: Desværre, db2 kan ikke finde ud af (Value=0) i order-by-delen.

I have a solution! Convert to Access :o) (joke!)

Yes, Access is a joke....  :o)

doesnt db2 have its own built in functions like most other versions of SQL?
I am actually surprised that proaccess' answer doesnt work!

Hva' så med:

SELECT *, IFNULL(NULLIF(Value, 0), 100) FROM Tabel1 ORDER BY IFNULL(NULLIF(Value, 0), 100), Value

Den virker på DB2 (på en AS400)...  Jeg er selv MEGET overrasket over manglende boolean funktion a'la (Value=0)...

AS400! do they still make them? Years since i played around with those!

Her er de fejlmeddelelser jeg får (anden tabel, men ideen er den samme):

db2 =>  select * from html  order by (html=10) desc, html ;
SQL0104N  An unexpected token "=" was found following "html  order by (html".
Expected tokens may include:  "+".  SQLSTATE=42601

og

db2 =>  SELECT  IFNULL(NULLIF(html,0), 100) FROM html ORDER BY IFNULL(NULLIF(html, 0), 100), html ;
SQL0171N  The data type, length or value of argument "2" of routine
"SYSIBM.NULLIF" is incorrect.  SQLSTATE=42815

Hvordan pokker kan jeg se hvilke argumenter en funktion tager?

Not sure if it will help but try replacing
(html=10) with ([html]=0)

er datatypen for html en talværdi?

SELECT *, IFNULL(NULLIF(html, 0), 100) FROM html ORDER BY IFNULL(NULLIF(html, 0), 100), html;

NULLIF returnerer NULL hvis første element er lig andet element, ellers returneres første element.
IFNULL returnerer første element, hvis dette er andet end NULL ellers returneres andet element.

Sådan er det ihvertfald i den version jeg arbejder med (gammel udgave og på AS/400)

Jeg ser yderligere en mulig konflikt i at din tabel og den enkelte kolonne hedder det samme !!!

Arrgg.... jeg dummede mig lidt, html er ikke af typen integer. Men hvis jeg bruger id som er, så får jeg stadig fejl:

db2 =>  select * from html order by (id=0) desc, id ;
SQL0104N  An unexpected token "=" was found following "om html order by (id".
Expected tokens may include:  "+".  SQLSTATE=42601
db2 => SELECT IFNULL(NULLIF(id,0), 100) FROM html ORDER BY IFNULL(NULLIF(id, 0), 100), id;
SQL0440N  No function by the name "IFNULL" having compatible arguments was
found in the function path.  SQLSTATE=42884
db2 => SELECT *,IFNULL(NULLIF(id,0), 100) FROM html ORDER BY IFNULL(NULLIF(id, 0), 100), id;
SQL0104N  An unexpected token "*" was found following "SELECT ".  Expected
tokens may include:  "?".  SQLSTATE=42601
db2 => describe table html ;

Column                        Type      Type
name                          schema    name              Length  Scale Nulls
------------------------------ --------- ------------------ -------- ----- -----
ID                            SYSIBM    INTEGER                  4    0 No
HTML                          SYSIBM    VARCHAR                  50    0 Yes

  2 record(s) selected.

??? - Prøv med:

SELECT ID, HTML, IFNULL(NULLIF(ID, 0), 100)
FROM HTML
ORDER BY IFNULL(NULLIF(ID, 0), 100), ID;

Hmmm... alternativt kan jeg jo blive nødt til at lave en udf (user defined function), der mapper værdierne korrekt. Men dette vil jeg helst undgå, for der er vel en del overhead i at kalde en extern javafunktion?

proaccess: sorry, men den brokker sig stadig over argumenterne til ifnull().

Sorry, så er jeg fuldstændig tom for ideer... ;0(

Du kan prøve at lave det via en union:

select name, 100 from tbl where value=0
union
select name, value from tbl where value<>0
order by value

det skulle have været:

select name, value, 100 as srtorder from tbl where value=0
union
select name, value, value from tbl where value<>0
order by  srtorder

>hansk: Nydelig, nem og indlysende løsning...  ;0)

  ... ORDER BY srtorder, value;

proaccess: lige mine ord.

hansk: perfekt!