This feature has been implemented during Hibernate 4.2.x and 4.3.x releases as previously mentioned. It can be used as for example: Criteria criteria = …; criteria.addOrder( Order.desc( “name” ).nulls(NullPrecedence.FIRST) ); Hibernate v4.3 javadocs are less omissive here.
order by case when name LIKE “%John%” then 1 when name LIKE “%Doe%” then 2 else 3 end
I think you may be looking for function FIELD — while normally thought of as a string function, it works fine for numbers, too! ORDER BY FIELD(field_name, 3,2,5,7,8,1)
SELECT id, name FROM mytable WHERE name IN (‘B’, ‘A’, ‘D’, ‘E’, ‘C’) ORDER BY FIELD(name, ‘B’, ‘A’, ‘D’, ‘E’, ‘C’) The FIELD function returns the position of the first string in the remaining list of strings. However, it is much better performance-wise to have an indexed column that represents your sort order, and then … Read more
var u = from u in db.users orderby u.username == 100 ? 1 : 0 descending select u;
For a simple query like this Postgres will use an index scan and retrieve readily sorted tuples from the index in order. Due to its MVCC model Postgres had to always visit the “heap” (data pages) additionally to verify entries are actually visible to the current transaction. Quoting the Postgres Wiki on index-only scans: PostgreSQL … Read more
You cannot specify offset in DELETE‘s LIMIT clause. So the only way to do that is to rewrite your query to something like: DELETE FROM `chat_messages` WHERE `id` IN ( SELECT `id` FROM ( SELECT `id` FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50 ) AS `x` ) Supposing that you have primary key … Read more
You should be able to do something like this: SELECT * FROM TableName WHERE (Forename LIKE ‘%’ + @SearchValue + ‘%’) OR (Surname LIKE ‘%’ + @SearchValue + ‘%’) OR (@SearchValue=”ALL”) ORDER BY CASE @OrderByColumn WHEN 1 THEN Forename WHEN 2 THEN Surname END; Assign 1 to @OrderByColumn to sort on Forename. Assign 2 to … Read more
It seems that your date column is not of type datetime but varchar. You have to convert it to datetime when sorting: select date from tbemp order by convert(datetime, date, 103) ASC style 103 = dd/MM/yyyy (msdn)
Going on the assumption it’s always WORD_space_NUMBER this should work: SELECT * FROM table ORDER BY CAST(SUBSTRING(column,LOCATE(‘ ‘,column)+1) AS SIGNED) Use POSITION to find the space, SUBSTRING to grab the number after it, and CAST to make it a comparable value. If there is a different pattern to the column, let me know and I’ll … Read more