SQL: How to save order in sql query?

Question

I have PostgreSQL database and I try to print all my users (Person).

When I execute this query

-- show owners 
-- sorted by maximum cars amount
SELECT p.id
  FROM car c JOIN person p ON c.person_id = p.id
  GROUP BY p.id
  ORDER BY COUNT(p.name) ASC;

I get all owners sorted by cars amount

Output: 3 2 4 1

And all order goes wrong when I try to link owner id.

SELECT *
FROM person p
WHERE p.id IN (
  SELECT p.id
  FROM car c JOIN person p ON c.person_id = p.id
  GROUP BY p.id
  ORDER BY COUNT(p.name) ASC);

Output: 1 2 3 4 and other data

You see than order is wrong. So here is my question how can I save that order?

Why do you need to complicate things and use the second way when the first way works just fine? — jarlh, Jan 03 '15 at 14:31

score 2 · Answer 1 · answered Jan 03 '15 at 14:25

2

Instead Of subquery use join. Try this.

SELECT p.*
FROM   person p
       JOIN (SELECT p.id,
                    Count(p.NAME)cnt
             FROM   car c
                    JOIN person p
                      ON c.person_id = p.id
             GROUP  BY p.id) b
         ON p.id = b.id
ORDER  BY cnt ASC

answered Jan 03 '15 at 14:25

Pரதீப்

91,748
19
131
172

Oh why do you use `p.*` instead of `*`? – barbara Jan 03 '15 at 14:32
To get only the p columns in the result. – jarlh Jan 03 '15 at 14:33
You don't need the `join` in the subquery. – Gordon Linoff Jan 03 '15 at 14:55
@Gordon Linoff Ok, and how can I connect two tables without join operation? – barbara Jan 03 '15 at 14:58

Erwin Brandstetter · Accepted Answer · 2023-05-16T02:13:12.780

Untangle the mess. Aggregate first, join later:

SELECT p.*
FROM   person p
JOIN  (
   SELECT person_id, count(*) AS ct
   FROM   car
   GROUP  BY person_id
   ) c ON c.person_id = p.id
ORDER  BY c.cnt;

See:

No need to join to person twice. This should be fastest if you count most or all rows.
For a small selection, correlated subqueries are faster:

SELECT p.*
FROM   person p
ORDER  BY (SELECT count(*) FROM car c WHERE c.person_id = p.id)
WHERE  p.id BETWEEN 10 AND 20;  -- some very selective predicate

As for your original: IN takes a set on the right hand, order of elements is ignored, so ORDER BY is pointless in the subquery.

SQL: How to save order in sql query?

2 Answers2

Linked