CODEWITHSUNDEEP
sqlsqlite
kinesis
kinesis

Reputation: 403

How to call query to move row location to Last?

Is there anyway to move specific row location to the last row without changing other rows location? Currently I use CASE WHEN STATEMENT, it can moves to the last row but the other rows' location are also changed. This is the demo database

    | 1 | John |
    | 2 | Mark |
    | 3 | Matt |
    | 4 | Zin  |
    | 5 | Teju |
    | 6 | Yolo |

The query is SELECT * FROM Transaction_table ORDER BY (CASE WHEN name = 'John' THEN 6 END), name. The result becomes like this

    | 1 | Yolo |
    | 2 | Mark |
    | 3 | Zin  |
    | 4 | Matt |
    | 5 | Teju |
    | 6 | John |

I just want the other rows remain untouched.

Upvotes: 0

Views: 419

Answers (2)

MikeT
MikeT

Reputation: 57043

I believe you can use something along the lines of :-

WITH namecte AS (SELECT 'Teju') /*<<<<<<<<<< change name to required name to appear last */
SELECT 
    (SELECT max(rowid) FROM transaction_table) + 1 AS sortid,
    *,
    rowid AS original_rowid 
    FROM transaction_table 
    WHERE name = (SELECT * FROM namecte)
UNION SELECT 
    rowid AS sortid,
    *,
    rowid AS original_rowid 
    FROM transaction_table 
    WHERE name <> (SELECT * FROM namecte) 
    ORDER BY sortid
;

This assumes that the table is not a WITHOUT ROWID table and that the order is the insertion order and hence the use of rowid (which builds sortid).

  • Of course another column could be used instead of rowid should such a column better denote what is considered first.....last (theoretically there is no concept of order unless ORDER BY is used) although the generation of sortid may need tweaking to suit the contents of the column.
  • original_rowid exists purely to demonstrate and would likely be omitted (although this may be of use as it is the actual rowid and can be used to locate the row if subsequent procsssing is required)

Demo/Test

DROP TABLE IF EXISTS transaction_table;
CREATE TABLE IF NOT EXISTS transaction_table (name TEXT);
INSERT INTO transaction_table VALUES('John'),('Mark'),('Matt'),('Zin'),('Teju'),('Yolo');
WITH namecte AS (SELECT 'John')
SELECT 
    (SELECT max(rowid) FROM transaction_table) + 1 AS sortid,
    *,
    rowid AS original_rowid 
    FROM transaction_table 
    WHERE name = (SELECT * FROM namecte)
UNION SELECT 
    rowid AS sortid,
    *,
    rowid AS original_rowid 
    FROM transaction_table 
    WHERE name <> (SELECT * FROM namecte) 
    ORDER BY sortid
;
/* Cleanup Testing Environment */
DROP TABLE IF EXISTS transaction_table;

enter image description here

Changing to Zin :-

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you want John to be last and the others ordered by name, then use two keys, but like this:

order by (case when name = 'John' then 2 else 1 end),  -- put John last
         name

Your original rows, however, are not sorted by name. Without an order by, a result set is unordered and the same query can return rows in any arbitrary order.

So, the first expression puts John last, but you still need the second key to define the ordering of the other rows.

Upvotes: 1

Related Questions