Is it possible to sort rows by values from generate_series()?

Question

I multiply result rows of a SELECT with CROSS JOIN generate_series(1, max). My query is like:

select id, name
from person
CROSS JOIN generate_series(1, 4)
where <condition>;

I have this result:

id	name
1	name1
1	name1
1	name1
1	name1
2	name2
2	name2
2	name2
2	name2
3	name3
3	name3
3	name3
3	name3

I want the result like this:

id	name
1	name1
2	name2
3	name3
1	name1
2	name2
3	name3
1	name1
2	name2
3	name3
1	name1
2	name2
3	name3

Is that possible?

Answer 1

Sure, ORDER BY expressions do not have to be listed in the SELECT list.
Order by the generated value, then by whatever else you want to order by:

SELECT p.id, p.name
FROM person p
CROSS JOIN generate_series(1, 4) g(g)
WHERE p.id < 4 -- or whatever
ORDER BY g.g, p.id; -- !!!

Or, with minimal syntax (less safe against ambiguous identifiers):

SELECT id, name
FROM person, generate_series(1, 4) g
WHERE id < 4 -- or whatever
ORDER BY g, id; -- !!!

• Charlieface: Just thought I'd mention "ORDER BY expressions do not have to be listed in the SELECT list" except when DISTINCT is used (which it isn't here)