Select first 50 rows then order

Question

Is it possible to select the first 50 rows in Postgres with select * from yellow_tripdata_staging fetch first 50 rows only and after that sort the results by column?

If so, how?

edit: the table is really big, and is not really important which rows i get.

this question was because i was using Redash to visualise the data and was getting some weird order on the sorted results.then i realized that the column i was using to order was not numerical but char, which causes values like 11 and 10 to come before 2 and 3.

Im sorry for this dumb question

There is no such thing as "the first 50 rows" in a SQL table or unordered result set. You need to be more specific about what you want to accomplish. — Gordon Linoff, Aug 16 '17 at 22:09
oh yes, it was as you said in " just mean to fetch the "first" 50 rows according to current physical order" i forgot about this question — Fernando Castilla Ospina, Jan 12 '18 at 18:07

Erwin Brandstetter · Accepted Answer · 2023-03-29T21:08:50.577

It's not completely clear how your "first 50 rows" are identified and in what order they shall be returned. There is no "natural order" in tables of a relational database. No guarantees without explicit ORDER BY.

However, there is a current physical order of rows you can (ab-)use. Typically that's the order in which rows have been inserted - as long as nothing else has happened to that table. But the RDBMS is free to change the physical order any time, so the physical order is not reliable. Results can and will change with write operations to the table (including VACUUM or other utility commands).

Let's call your sort column sort_col.

(  -- parentheses required
TABLE yellow_tripdata_staging LIMIT 50
)
UNION ALL 
(  -- parentheses required
SELECT *
FROM  (TABLE yellow_tripdata_staging OFFSET 50) sub
ORDER  BY sort_col
);

More explanation (incl. TABLE and parentheses):

Or, assuming sort_col is defined NOT NULL:

SELECT *
FROM   yellow_tripdata_staging
ORDER  BY CASE WHEN row_number() OVER () > 50 THEN sort_col END NULLS FIRST;

The window function row_number() is allowed to appear in the ORDER BY clause.

row_number() OVER () (with empty OVER clause) will attach serial numbers according to the current physical order of row - all the disclaimers above still apply.

The CASE expression replaces the first 50 row numbers with NULL, which sort first due to attached NULLS FIRST. In effect, the first 50 rows are unsorted the rest is sorted by sort_col.

Or, if you actually mean to take the first 50 rows according to sort_col and leave them unsorted, while the rest is to be sorted:

SELECT *
FROM   yellow_tripdata_staging
ORDER  BY GREATEST (row_number() OVER (ORDER BY sort_col), 50);

Or, if you just mean to fetch the "first" 50 rows according to current physical order or some other undisclosed (more reliable) criteria, you need a subquery or CTE to sort those 50 rows in the outer SELECT:

SELECT *
FROM  (TABLE yellow_tripdata_staging LIMIT 50) sub
ORDER  BY sort_col;

You need to define your requirements clearly.

score 0 · Answer 2 · answered Aug 16 '17 at 22:11

You can order by two different columns. For instance:

select yts.*
from (select yts.*,
             row_number() over (order by id) as seqnum
      from yellow_tripdata_staging yts
     ) yts
order by (seqnum <= 50)::int desc,
         (case when seqnum <= 50 then id end),
         col

Select first 50 rows then order

2 Answers2