Select from table and keep all lines with a certain column value in the bottom of the result-CodePudding

I have the following table:

name	age
pedro	11
duda	12
joao	7
pedro	9
guga	10
pedro	13
zelda	10

What I want is to select all entries here in which age >= 10, but at the same time keep all results which name = pedro in the last position, like this:

name	age
duda	12
guga	10
zelda	10
pedro	11
pedro	13

So there are two questions:

is it possible to do it with a select statement?
if so, is it possible to do this with JPA Repository simply naming a method?

CodePudding user response：

is it possible to do it with a select statement?

Yes you can use where and order by with case like:

select *
from table
where age >= 10
order by case name when 'pedro' then 2 else 1 end

CodePudding user response：

You can use CASE WHEN in the ORDER BY

SELECt * FROM tab1
WHERE age >= 10
ORDER BY CASE WHEN `name` = 'Pedro' THen 2 ELSE 1 END ASC, `name` ASC, `age` ASC

name  | age
:---- | --:
duda  |  12
guga  |  10
zelda |  10
pedro |  11
pedro |  13

db<>fiddle here