You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value: ORDER BY CASE WHEN @OrderDirection = 0 THEN 1 ELSE CASE WHEN @OrderByColumn = ‘AddedDate’ THEN CONVERT(varchar(50), AddedDate) WHEN @OrderByColumn = ‘Visible’ THEN CONVERT(varchar(2), Visible) … Read more
Prior to 12.1, Oracle does not support the LIMIT or OFFSET keywords. If you want to retrieve rows N through M of a result set, you’d need something like: SELECT a.* FROM (SELECT b.*, rownum b_rownum FROM (SELECT c.* FROM some_table c ORDER BY some_column) b WHERE rownum <= <<upper limit>>) a WHERE b_rownum >= … 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
Ok, I THINK I understand what you want now, and let me clarify to confirm before the query. You want 1 record for each user. For each user, you want their BEST POINTS score record. Of the best points per user, you want the one with the best average time. Once you have all users … 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
version 1: SELECT max(id) id, name FROM cards WHERE name like ‘%John%’ GROUP BY name ORDER BY CASE WHEN name like ‘John %’ THEN 0 WHEN name like ‘John%’ THEN 1 WHEN name like ‘% John%’ THEN 2 ELSE 3 END, name version 2: SELECT max(id) id, name FROM cards WHERE name like ‘%John%’ GROUP … Read more
How can I do an orderby thenby using the above syntax without using extension syntax. Use a comma between the fields: orderby a, b And what does the orderby, orderby do? When you use orderby twice in a row the elements conceptually will first be sorted using the first orderby, and then sorted again using … Read more
order by case when name LIKE “%John%” then 1 when name LIKE “%Doe%” then 2 else 3 end
I somehow didn’t manage to run the query with CAST. I was always getting Error Code: 1064 near “DECIMAL” (or other numeric type that I chose). So, I found another way to sort varchar as numbers: SELECT * FROM mytable ORDER BY ABS(mycol) A bit simpler and works in my case.
If you want to avoid non-standard annotations, you could make kittens use some sorted Collection implementation. This would ensure that kittens is always in sorted order. Something like this: @Entity public class Cat { @OneToMany(mappedBy = “cat”, cascade = CascadeType.ALL) @OrderBy(“name ASC”) private SortedSet<Kitten> kittens = new TreeSet<>(); } Note that this approach also requires … Read more