ROW_NUMBER requires an ORDER BY clause syntactically. You cannot use it without one. SELECT NULL is a hack to shut up the error while not enforcing any particular order. In this case we don’t need to enforce any order, so the fastest option is to use SELECT NULL. The optimizer sees through this trick, so … Read more
As one can sort it out by reading AakashM answer and Nicolae Dascalu answer, it strongly seems Linq OrderBy requires a stable ranking function, which NewID/Guid.NewGuid is not. So we have to use another random generator that would be stable inside a single query. To achieve this, before each querying, use a .Net Random generator … Read more
The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer, one text. With the current table, you could: SELECT col FROM tbl ORDER BY (substring(col, ‘^[0-9]+’))::int — cast to integer , substring(col, ‘[^0-9_].*$’); — works as text The same substring() … Read more
I didn’t think this was possible, but found a blog entry here that seems to do the type of thing you’re after: SELECT id FROM table WHERE id in (7,2,5,9,8) ORDER BY FIND_IN_SET(id,”7,2,5,9,8″); will give different results to SELECT id FROM table WHERE id in (7,2,5,9,8) ORDER BY FIND_IN_SET(id,”8,2,5,9,7″); FIND_IN_SET returns the position of id … Read more
Not supported, input parameters are only allowed in the WHERE and HAVING clauses and you cannot use parameters for the ORDER BY clause. Or if I rephrase, you can’t use parameters for columns, only values. So, either: Have as much named queries as possible sort orders Concatenate the ordering string to the query string Use … Read more
Another method: SELECT * FROM ( SELECT * FROM table_name ORDER BY date ASC, time ASC ) AS sub GROUP BY name GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected. I prefer this method as … Read more
You need to aggregate the data first, this can be done using the GROUP BY clause: SELECT Group, COUNT(*) FROM table GROUP BY Group ORDER BY COUNT(*) DESC The DESC keyword allows you to show the highest count first, ORDER BY by default orders in ascending order which would show the lowest count first.
Actually, with default sort order (ASCENDING) NULL values come last. Logic dictates that the sort order be reversed with the DESCENDING keyword, so NULLs come first in this case. But the best part comes last: you can choose which way you want it: Use the NULLS FIRST | LAST clause. Quoting the current manual, version … Read more
Although it’s good to wonder about how it might be explained that you often see the same order, I’d like to point out that it never a good idea to rely on implicit order caused by the particular implementation of the underlying database engine. In other words, its nice to know why, but you should … Read more
Since Postgres 9.6, it is possible to specify a collation which will sort columns with numbers naturally. https://www.postgresql.org/docs/10/collation.html — First create a collation with numeric sorting CREATE COLLATION numeric (provider = icu, locale=”en@colNumeric=yes”); — Alter table to use the collation ALTER TABLE “employees” ALTER COLUMN “em_code” type TEXT COLLATE numeric; Now just query as you … Read more