DB queries with "In" seem to be sorted-CodePudding

There are clients(companies) and contacts in two tables. Both contacts and clients have their own fields. But they all have an id that is a primaryKey. In addition, there are "links" between clients and contacts - each client can be assigned a contact and vice versa. There is a third table for this, called 'assignment', which has only two fields - company id and contact id.

Suppose we have linked a contact with id 3 to a company with id 12076. Next, we have linked a contact with id 10, and the last contact with id 5. Сheck our result:

SELECT contactId FROM assignment WHERE companyId = 12076;

The result is correct -

After these steps, at some point I need to get the FIELDS of contacts associated with the company I need. There is a company 12076, I need to get all the contact fields associated with it. I made a request like this:

SELECT id, fullName FROM contacts WHERE contacts.rowid IN (SELECT contactId FROM assignment WHERE companyId = 12076);

This request displays the contacts I need and their fields. Exactly 3 contacts, but there is one problem - they are displayed in sorted order, sorted by id.

I need the contact fields to be displayed in the order in which they were added. How can i dow this? Maybe i need to use JOIN?

CodePudding user response：

By default an SQLite table has a Rowid column. We can use this column in ORDER BY. If we want the ContactId's in the order they were inserted into the table contacts we can join onto contacts and order by it's Rowid.
See [https://www.sqlitetutorial.net/sqlite-autoincrement/][1]

create table companies(id int);
create table contacts (id int);
create table assignment(companyId int,contactId int);
insert into companies values(12076);
insert into contacts values(10),(3),(5);
insert into assignment values (12076,3),(12076,10),(12076,5);

SELECT a.contactId 
FROM assignment a
JOIN contacts c
on a.contactId = c.id
WHERE companyId = 12076
order by c.Rowid;

| contactId |
| --------: |
|        10 |
|         3 |
|         5 |

db<>fiddle here