Return rows or records in a table with random order in SQLite
Let’s take a simple table for this:
CREATE TABLE user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
INSERT INTO user (name) VALUES ('abc'), ('def'), ('ghi'), ('jkl'), ('mno');
SELECT * FROM user ORDER BY random();
This will return the rows in a random order
You can limit the number of rows
SELECT * FROM user ORDER BY random() LIMIT 5;
We can even randomly shuffle a subset of rows with limit and where clauses
The random function in sqlite is quite handy to generate pseudo random numbers.
The random function returns a value between -9223372036854775807 and +9223372036854775807
SELECT CAST(power(2,63) AS INTEGER);
SELECT CAST(-power(2,63) AS INTEGER);
Note that it doesn’t return the max integer as 9223372036854775808 becuase using those might break where integer limit might overflow.
This was actually documentated incorrectly before 25th July 2025, that’s quite recent.
We can use that to get absolute values, that is to avoid negative values and then mod (divide and get the remainder) it with the max number we want to generate upto.
A simple yet rewarding week. Continuing the learning path of SQL, and taking it to a next level with consistent posting of log like posts, reading a ton of hackernews articles, researching about AI generated images metadata, and a lot of python code.
I will try to take a break this weekend, try to think what I want to see myself in the next 5 years, 3 years, and 1 year. I really want to think what I am and want to be as a person. Thanks to the post that I read this week, also will continue with SQL lessons or even take a course or certification.
After taking a look at what the Generate Series function does, now let’s see some examples that might be handy.
To generate a series of numbers from 1 to 5.
SELECT * FROM generate_series(1, 5);
To generate a series of numbers from 0 to 50 in steps of 10.
SELECT * FROM generate_series(0, 50, 10);
This will start from 0, increment by 10 till 50.
0
10
20
30
40
50
To generate a series from 50 to 0 in steps of 10
The generate_series is a table valued function in sqlite and is available via the generate_series extension.
The valued function is something that returns a table but is virtual (doesn’t really have data or schema in it). It has hidden columns which are used as parameters to the function to constrain the output and generate the data according to those parameters to the function.
The basic operation with generate_series would look like this:
The once dot command is similar to the output, however the distinction is that it is limited to the very next SQL query and not all the subsequent queries.
The primary way to use once is either by specifying the specific file name/path or opening the result set in the system editor.
.once somefile.txt
Once this is set, any query you execute, the result set of it will be logged to the specified file.
I will be starting to post something I learn daily about sql,sqlite or anything sql.
There is so much to learn!
Today I found you can output a result of query in sqlite shell with
.output filename
This will start appending the result of all the queries executed in the shell to the file. It will preserve all the modes and configuration used for that specific instance of the shell. Basically just the output you would see after executing the query n the shell, it will dumo that to the mentioned file (it will not output to the screen/io as it will dump in the file)
A clean week, it was a festive week. Saturday was Raksha Bandhan, so almost half of the day was spent in those rituals and getting in and out of the house and guests.
Sunday was mundane as I just sat back and took a break to detox the technical things I have been doing over that last week. Then hard hitting monday came where I was frustrated with AI vibes, that left me a headache and sore throat for the next day. Took a leave for the day and was feeling better on Wednesday, cradled a few more vibe coding sessions, this time more focused on my code expertise and drove the LLM to do certain things in certain limited ways, and it worked better.
What a week! LLM models all over the place, gpt this, gpt that.Anyways, I find myself in a better place by avoiding AI in some places and using at its full potential at certain place, it took some time to realise it, but its kind of working now.
I don’t use AI to build the side projects like one shot, but use AI to help me understand certain topic, do something myself and converse with it about what it means, and what gaps I have in it. Using AI (cursor) at my work to speed things up and staying calm and letting things happen.
I ran a few tests on the 20B parameter
Some of the live footage is here:
42