I have this simple table and user records.
I just want to get users with ilike or something else to get better order in PostgreSQL 10.12.
CREATE TABLE users(
ID INT PRIMARY KEY NOT NULL,
username CHAR(50) NOT NULL
);
INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');
When I query "del" I want to get "del" user whose id is 4 in first place. it's ok with ordering by length. But still it is not what I want. The order should be like this. Second order must be "dell", third order must be "delpiero", fourth "andel", fifth "gondela" and sixth "mandela".
select * from users where username ilike '%del%' order by length(users.username) asc;
Best Answer
I would seem to me, that you want leading matches first, then shortest strings first, and finally alphabetic sort order:
db<>fiddle here
NOT ILIKE 'del%'
becausefalse
sorts beforetrue
. See:Aside: Never use
char(n)
. See: