Sort by certain data range

I have table data like below. There are 4 columns: DATE, SLIP_NO, ORDER_NO, ITEM_NAME
・Data are being displayed in the order they were registered to the database, which records were registered first are displayed on top.
・There can be multiple ORDER_NO values that are empty.

DATE	SLIP_NO	ORDER_NO	ITEM_NAME
01-08-2023	006983	J13828	MIY-000-003
01-08-2023	006984	J13830	MIY-000-013
01-08-2023	006985	J13829	MIY-000-005
01-08-2023	076672		MIY-000-010
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13840	MIY-000-015
22-08-2023	006997	J13855	MIY-000-014
31-08-2023	000035		TAX 10%
31-08-2023	000034		TAX 8%

I want to sort them like this

DATE	SLIP_NO	ORDER_NO	ITEM_NAME
01-08-2023	006983	J13828	MIY-000-003
01-08-2023	006984	J13830	MIY-000-013
01-08-2023	006985	J13829	MIY-000-005
01-08-2023	076672		MIY-000-010
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13840	MIY-000-015
22-08-2023	006997	J13855	MIY-000-014
31-08-2023	000034		TAX 8%
31-08-2023	000035		TAX 10%

The two lines TAX 8% and TAX 10% are always displayed at the bottom and it is the total amount of 8% or 10% tax of the items above it.

I would like to be able to change the display order of the two rows 8% and 10% without changing the order of the rows above 8% and 10%. Is this possible?
※I want to sort the last 2 rows based on the ITEM_NAME column and not based on the SLIP_NO column, because the value of column SLIP_NO(8%) can be smaller or larger than column SLIP_NO(10%)

My query:

SELECT DISTINCT C.DATE, C.SLIP_NO, C.ORDER_NO, C.ITEM_NAME
FROM BILL_PDF_TBL A 
    INNER JOIN BILL_TBL B ON A.PDF_ID = B.BILL_NO
    INNER JOIN BILL_DETAIL C ON B.BILL_NO = C.BILL_NO AND A.PDF_ID = C.BILL_NO

■■■Why not simply use ORDER BY date, slip_no? What's wrong with this?
＞Because the value of column SLIP_NO(8%) can be smaller or larger than colum SLIP_NO(10%). For example:

DATE	SLIP_NO	ORDER_NO	ITEM_NAME
31-08-2023	000034		TAX 10%
31-08-2023	000035		TAX 8%

■■■How to sort the data if other rows with Tax 7%, Tax 16% etc. appear or if further Tax 8% rows appear having different values in the other columns?
＞ITEM_NAME that contains the word TAX is always inserted last so I only want to sort ITEM_NAME rows that contain the word TAX without changing the order of ITEM_NAME rows that do not contain the word TAX of the items above it. For example:
Before:

DATE	SLIP_NO	ORDER_NO	ITEM_NAME
01-08-2023	006983	J13828	MIY-000-003
01-08-2023	006984	J13830	MIY-000-013
01-08-2023	006985	J13829	MIY-000-005
01-08-2023	076672		MIY-000-010
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13840	MIY-000-015
22-08-2023	006997	J13855	MIY-000-014
31-08-2023	000034		TAX 8%
31-08-2023	000035		TAX 16%
31-08-2023	000037		TAX 10%
31-08-2023	000036		TAX 7%

After:

DATE	SLIP_NO	ORDER_NO	ITEM_NAME
01-08-2023	006983	J13828	MIY-000-003
01-08-2023	006984	J13830	MIY-000-013
01-08-2023	006985	J13829	MIY-000-005
01-08-2023	076672		MIY-000-010
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13838	MIY-000-005
08-08-2023	006986	J13840	MIY-000-015
22-08-2023	006997	J13855	MIY-000-014
31-08-2023	000036		TAX 7%
31-08-2023	000034		TAX 8%
31-08-2023	000037		TAX 10%
31-08-2023	000035		TAX 16%

■■■What to do if let's say a further row 15-09-2023,006999,J13860,MIY-000-018 exists? Should this row be sorted before or after the two "tax rows"?
＞This is 1 month's invoice data, so there is only August data, no other month data, and the tax rows will be calculated on the last day of the month then inserted last.

Solution

After all I read, it seems to me you want to apply following sort: First by date, then secondary by tax percentage if existing, and last by slip_no.

I think a good idea to cover this is to create a tax table which holds the possible taxes with their percentage. Something like this:

CREATE TABLE taxes (tax_name varchar(100), percentage int);
INSERT INTO taxes VALUES ('TAX 7%',7);
INSERT INTO taxes VALUES ('TAX 8%',8);
INSERT INTO taxes VALUES ('TAX 10%',10);
INSERT INTO taxes VALUES ('TAX 16%',16);

Then we can use a LEFT JOIN to the other table(s) and sort by the tax percentage:

SELECT 
  bd.date, bd.slip_no, bd.order_no, bd.item_name
FROM
  bill_detail bd LEFT JOIN taxes t
    ON bd.item_name = t.tax_name
ORDER BY bd.date, t.percentage, bd.slip_no;

(bd.date and t.percentage might be switched in the ORDER BY clause, but I guess, you want to sort by date first. Change it otherwise)

This query will produce the result you described, try out on this db<>fiddle based on your sample data.

Note: Of course, you don't urgently need another table, you could use a substring function to fetch the percentage out of the item name and sort by it. But in my opinion, that's both bad readable and very unclean. It would maybe even be better to alter the original table by a tax percentage or tax id column and join with that column rather than the item name column. But I focused on answering your question, not rewriting your DB design.

If you are not allowed to create a new table, you can use a CTE instead:

WITH taxes AS
  (SELECT 'Tax 7%' AS tax_name, 7 AS percentage
  UNION ALL
  SELECT 'Tax 8%', 8
  UNION ALL
  SELECT 'Tax 10%', 10
  UNION ALL
  SELECT 'Tax 16%', 16)
SELECT 
  bd.date, bd.slip_no, bd.order_no, bd.item_name
  FROM
    bill_detail bd LEFT JOIN taxes t
      ON bd.item_name = t.tax_name
ORDER BY bd.date, t.percentage, bd.slip_no;

The fiddle shows this creates the same result.