LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-14-2022, 10:05 AM   #1
lucmove
Senior Member
 
Registered: Aug 2005
Location: Brazil
Distribution: Debian
Posts: 1,272

Rep: Reputation: 90
Can I invert the order of a column in SQLite?


I made this table:

Code:
1 field1 field2 field3 field4
2 field1 field2 field3 field4
3 field1 field2 field3 field4
4 field1 field2 field3 field4
The first column was actually inserted. The DB viewer also shows a row line number, but I have my own column.

Now I want this:

Code:
4 field1 field2 field3 field4
3 field1 field2 field3 field4
2 field1 field2 field3 field4
1 field1 field2 field3 field4
...without changing the order of the other columns!

The original source has been deleted.

I guess I can export, reorder and create a new table, but I wonder if there is a native SQLite statement that will do that. It would be good to know.

TIA

Last edited by lucmove; 11-14-2022 at 10:08 AM.
 
Old 11-14-2022, 10:13 AM   #2
boughtonp
Senior Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 2,983

Rep: Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120

You appear to be asking a really trivial question that can be answered extensively by putting your thread title into a search engine.

If that is not the case, explain more clearly what you're trying to do and why.

 
Old 11-14-2022, 10:17 AM   #3
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,387
Blog Entries: 1

Rep: Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665
@OP I think you are mixing terms 'row' and 'column'
 
Old 11-14-2022, 10:26 AM   #4
michaelk
Moderator
 
Registered: Aug 2002
Posts: 23,677

Rep: Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184
By default sqlite tables when created will automatically have a unique column called rowid unless you specify WITHOUT ROWID. It will autoincrement as rows are inserted.

Without doing anything you can order the data by rowid i.e.

select * from table order by rowid desc
 
1 members found this post helpful.
Old 11-14-2022, 11:10 AM   #5
lucmove
Senior Member
 
Registered: Aug 2005
Location: Brazil
Distribution: Debian
Posts: 1,272

Original Poster
Rep: Reputation: 90
Quote:
Originally Posted by boughtonp View Post
You appear to be asking a really trivial question that can be answered extensively by putting your thread title into a search engine.
If that is not the case, explain more clearly what you're trying to do and why.
The data has chronological order. But I made a mistake when I inserted the rows. I added my own "row count" and I inserted the newest records first. I should have inserted the oldest ones first. One of the columns is a Unix/epoch timestamp so it's fixable with very little programming. I am going to do it by the end of the day.

But I think it looks like something that the database software probably can do on its own with the right commands. And if it can, I want to learn how.

Quote:
Originally Posted by NevemTeve View Post
@OP I think you are mixing terms 'row' and 'column'
No.

Quote:
Originally Posted by michaelk View Post
By default sqlite tables when created will automatically have a unique column called rowid unless you specify WITHOUT ROWID. It will autoincrement as rows are inserted.
Without doing anything you can order the data by rowid i.e.
select * from table order by rowid desc
Yes, that saves the day when doing SELECTs. Thank you!
But the data still looks wrong to me because I inserted it wrong. Like I said, I can export, parse, change and recreate, but I wonder if SQLite has any command to do that without my external maneuvering.
 
Old 11-14-2022, 11:26 AM   #6
michaelk
Moderator
 
Registered: Aug 2002
Posts: 23,677

Rep: Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184
I am not all that familiar with sqlite but I believe you can create a second table and copy but I do not know the exact syntax.

insert into new_table select * from old_table order by rowid desc;

Delete the contents of the old table and then insert the data back.

If you are using a timestamp then you really do not have a problem except it looks wrong to you.

Last edited by michaelk; 11-14-2022 at 11:28 AM.
 
1 members found this post helpful.
Old 11-14-2022, 11:37 AM   #7
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,387
Blog Entries: 1

Rep: Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665Reputation: 1665
> > I think you are mixing terms 'row' and 'column'

> No

Well, you wish to change the order in which you fetch the rows when you do SELECT without ORDER BY. My advice here: don't use SELECT without ORDER BY.
 
2 members found this post helpful.
Old 11-14-2022, 11:46 AM   #8
michaelk
Moderator
 
Registered: Aug 2002
Posts: 23,677

Rep: Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184Reputation: 5184
Its not a big thing. The OP can also export the data to CSV with the correct order, delete the contents of the database and import back.
 
Old 11-14-2022, 01:11 PM   #9
boughtonp
Senior Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 2,983

Rep: Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120
Quote:
Originally Posted by lucmove View Post
The data has chronological order. But I made a mistake when I inserted the rows. I added my own "row count" and I inserted the newest records first. I should have inserted the oldest ones first. One of the columns is a Unix/epoch timestamp so it's fixable with very little programming.
So you want to change the internal rowid based on your timestamp column?

A search for "sqlite change internal rowid" leads to SQLite documentation which points out:
Quote:
Originally Posted by https://www.sqlite.org/rowidtable.html
The rowid of a rowid table can be accessed (or changed) by reading or writing to any of the "rowid" or "oid" or "_rowid_" columns
Thus one can simply use an standard update statement that inverts the values in that column, without needing a temporary table.

 
Old 11-14-2022, 01:35 PM   #10
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=15, FreeBSD_12{.0|.1}
Posts: 5,973
Blog Entries: 23

Rep: Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945Reputation: 3945
Actually, you should never depend on the stored order of relational data for anything.

By definition, the stored order of relational data is indeterminate and is entirely a concern of the management system, or engine which provides access and is opaque to the user. Ordering is, or should be entirely determined by the query which asks the engine for the data.

That said, SQLite and other file based engines expose the storage structure and that exposure is exploited by the models implemented on top of it, which is what you are apparently trying to do.

That may work for you and that is fine (this is not a criticism), but you should be aware that this may make your model non-portable to other management systems (MySQL, PostgreSQL for example) and will definitely make queries which depend on that ordering non-portable, not just to other RDBMS's, but possibly to future versions of SQLite (unless its spec guarantees that ordering).

When you say the data still looks wrong to you I assume you mean because you inserted it out of chronological order. If your SELECT statement includes an ORDER BY timestamp-column clause, as it should (must to guarantee ordering), then the rows will be rertieved in chronological order, not insert rowid order.

What you are really asking I think, is how to make the rowid numerical order dependent on, or correspond to, timestamp-column order, which you could do by suitable query or by temporarily duplicating the table as suggested by michaelk. But that creates a kind of redundancy in the data which should be avoided in relational data as it can create ambiguities and other problems.
 
3 members found this post helpful.
Old 11-14-2022, 02:15 PM   #11
dugan
LQ Guru
 
Registered: Nov 2003
Location: Canada
Distribution: distro hopper
Posts: 10,701

Rep: Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062Reputation: 5062
The whole point of a relational database is that you don’t need to do that.

But if you really want to, then just delete the rows and reinsert them.
 
1 members found this post helpful.
Old 11-16-2022, 02:10 AM   #12
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Posts: 952

Rep: Reputation: 217Reputation: 217Reputation: 217
Pseudo code:
Quote:
T=select count(*) from TABLEE
(for I=T;I==1;I--) Update TABLEEE set FIELD0=I
OK
 
Old 11-16-2022, 09:04 AM   #13
boughtonp
Senior Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 2,983

Rep: Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120
Quote:
Originally Posted by AnanthaP View Post
Pseudo code:
Quote:
T=select count(*) from TABLEE
(for I=T;I==1;I--) Update TABLEEE set FIELD0=I
OK
Actual code:
Code:
update tablename set rowid = -rowid

As suggested, this is only relevant for the default view in an SQL browser - any queries where order matters should be explicitly ordering on the timestamp (or whatever column is relevant).

 
Old 11-16-2022, 12:46 PM   #14
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 10,007
Blog Entries: 4

Rep: Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607Reputation: 3607
As @astrogeek very well said, the physical ordering of elements within a table must always be regarded as: "entirely unpredictable, and irrelevant." Likewise, the primary key (call it "rowid" if you like ...) is intended to be "as meaningless as a non-vanity automobile license-plate number."

If you then want the rows to appear in some "meaningful to you" order, then specify that ORDER BY each and every time. (If you do this frequently, create an "index" on that column, or set of columns.) The "order column" should be part of your data.

If you need to order the data by "the date-and-time when it was inserted into the database," create a "timestamp" column and arrange for all of your queries to insert the value of NOW() into it ... unless the database provides this as a convenient default option. (I don't right-now recall if SQLite provides this fee-chur ... and I'm too lazy to find out.)

Last edited by sundialsvcs; 11-22-2022 at 04:40 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: How to Install SQLite and SQLite Browser on Ubuntu 20.04 LXer Syndicated Linux News 0 04-25-2021 08:36 AM
Logi Sales Manager on Ncurses (invoice, invoicing, orders, order, sale order, sales order...)? Xeratul Linux - Software 0 03-25-2017 03:45 PM
LXer: Sqlite-Commander - A ncurses based tool to display the records and tables of a sqlite database LXer Syndicated Linux News 0 01-02-2011 09:11 AM
iptables invert issue lappen Linux - Software 8 09-26-2004 03:19 PM
terminal colors invert gkhattak Linux - Newbie 0 08-29-2002 10:42 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 05:12 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration