The order_by(‘?’)[:2] solution suggested by other answers is actually an extraordinarily bad thing to do for tables that have large numbers of rows. It results in an ORDER BY RAND() SQL query. As an example, here’s how mysql handles that (the situation is not much different for other databases). Imagine your table has one billion … 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
You can use case expression: ORDER BY CASE WHEN Column1 IS NOT NULL THEN Column1 ELSE Column2 END Or the corresponding syntactic sugar: ORDER BY COALESCE(Column1, Column2) The result will be like: column1 | column2 ——–|——– 1 | 1258 2 | 5972 null | 3 4 | 3698 5 | 7524 Note that the datatype … 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.
This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement. According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least … Read more
It’s possible. Here’s an extension method: public static bool IsOrdered<T>(this IQueryable<T> queryable) { if (queryable == null) { throw new ArgumentNullException(nameof(queryable)); } return queryable.Expression.Type == typeof(IOrderedQueryable<T>); }
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
It’s incredibly clunky, but you can use a CASE statement for ordering: SELECT * FROM Blah ORDER BY CASE MyColumn WHEN ‘orange’ THEN 1 WHEN ‘apple’ THEN 2 WHEN ‘strawberry’ THEN 3 END Alternately, you can create a secondary table which contains the sort field and a sort order. TargetValue SortOrder orange 1 apple 2 … Read more
Have you tried using the DataTable.Select(filterExpression, sortExpression) method?
Start with this: select StudentId, max(DateApproved) from tbl group by StudentId Then integrate that to main query: select * from tbl where (StudentId, DateApproved) in ( select StudentId, max(DateApproved) from tbl group by StudentId ) You can also use this: select * from tbl join (select StudentId, max(DateApproved) as DateApproved from tbl group by StudentId) … Read more