Please explain tome why the third row of my result is not the fourth
row. In most Western alphabets 'dd'>'c'.

>[SQL] Script lines: 1-29 ---------------------------

SELECT t1.*,
lot_numb, lot_suffix, part_of_lot_numb, part_of_lot_suffix,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(lot_suffix, ''),
COALESCE(RTRIM(LTRIM(CHAR(t2.lot_numb))), '') ||
COALESCE(t2.lot_suffix, '') AS lot,
t3.namex,
CHAR(t3.birth_date) AS birth_datep,
t3.sex
FROM is3.auction_done_bids t1
JOIN is3.auction_items t2 ON t2.item_id=t1.item_id
JOIN is3.animals t3 ON t3.bhid=t2.bhid
WHERE t1.auction_id=21 AND t1.invoice_no=2
ORDER BY CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')
FOR READ ONLY 

AUCTION_ID BIDDER_ID ITEM_ID AMOUNT WHEN_BID
INVOICE_NO LOT_NUMB LOT_SUFFIX PART_OF_LOT_NUMB
PART_OF_LOT_SUFFIX 11 12 13 14 LOT NAMEX
BIRTH_DATEP SEX
------------- ------------ ---------- ---------
-------------------- ------------- ----------- -------------
------------------- --------------------- ----- ----- ----- -----
------ ------------------------------ -------------- ------
21 1 25 334 8/10/2006 6:35:15
PM 2 1 (null) (null)
(null) 1 1 1 NAME OF THIS
ANIMAL IS AAA8AAA 2005-11-01 C

21 1 23 333 8/10/2006 6:35:15
PM 2 1 a 1
(null) 1 1 a 1a (null)
(null) B

21 1 24 0 8/10/2006 6:35:15
PM 2 1 dd 1
(null) 1 1 dd 1dd (null)
2005-11-07 (null)

21 1 26 333 8/10/2006 6:35:15
PM 2 1 c 1
(null) 1 1 c 1c (null)
2000-11-01 C

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]

[Executed: 8/11/06 2:40:08 AM GMT ] [Execution: 172/ms]

Bob Stearns wrote:

Please explain tome why the third row of my result is not the fourth
row. In most Western alphabets 'dd'>'c'.
>

SELECT t1.*,
lot_numb, lot_suffix, part_of_lot_numb, part_of_lot_suffix,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(lot_suffix, ''),
COALESCE(RTRIM(LTRIM(CHAR(t2.lot_numb))), '') ||
COALESCE(t2.lot_suffix, '') AS lot,
t3.namex,
CHAR(t3.birth_date) AS birth_datep,
t3.sex
FROM is3.auction_done_bids t1
JOIN is3.auction_items t2 ON t2.item_id=t1.item_id
JOIN is3.animals t3 ON t3.bhid=t2.bhid
WHERE t1.auction_id=21 AND t1.invoice_no=2
ORDER BY CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')
FOR READ ONLY 
>
AUCTION_ID BIDDER_ID ITEM_ID AMOUNT WHEN_BID
INVOICE_NO LOT_NUMB LOT_SUFFIX PART_OF_LOT_NUMB
PART_OF_LOT_SUFFIX 11 12 13 14 LOT NAMEX
BIRTH_DATEP SEX
------------- ------------ ---------- ---------
-------------------- ------------- ----------- -------------
------------------- --------------------- ----- ----- ----- -----
------ ------------------------------ -------------- ------
21 1 25 334 8/10/2006 6:35:15
PM 2 1 (null) (null)
(null) 1 1 1 NAME OF THIS
ANIMAL IS AAA8AAA 2005-11-01 C
>
21 1 23 333 8/10/2006 6:35:15
PM 2 1 a 1
(null) 1 1 a 1a (null)
(null) B
>
21 1 24 0 8/10/2006 6:35:15
PM 2 1 dd 1
(null) 1 1 dd 1dd (null)
2005-11-07 (null)
>
21 1 26 333 8/10/2006 6:35:15
PM 2 1 c 1
(null) 1 1 c 1c (null)
2000-11-01 C
>
4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]
>
[Executed: 8/11/06 2:40:08 AM GMT ] [Execution: 172/ms]

In 2nd to 4th result rows, both of Expression1 and Expression2 of
ORDER BY were not satisfy WHEN condition(in those rows part_of_lot_numb
IS NOT NULL).
So, it is equivalent to
ORDER BY COALESCE(part_of_lot_numb, 0), COALESCE(part_of_lot_suffix,
''),
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')