CODEWITHSUNDEEP
javahibernatesortinghql
Ishwar Lal
Ishwar Lal

Reputation: 654

How to sort column by HQL if column have both string and integer values

I have a column "event_number" in "event" table. "event_number" column is actually varchar column in mysql.

I want to sort data according to "event_number" using HQL query. (Hibernate).

For example data is 

event_number
1
2
M-2
4
5
K-C
6
7
8
9
10
11
TY-C

The output should be:

event_number
1
2
4
5
6
7
8
9
10
11
K-C
M-2
TY-C

or The output should be:

event_number
K-C
M-2
TY-C
1
2
4
5
6
7
8
9
10
11

I have used HQL query:

select 
    event.eventNumber 
from 
    event 
order by 
    event.eventNumber asc;

I got result:

event_number
1
10
11
2
4
5
6
7
8
9
K-C
M-2
TY-C

Upvotes: 3

Views: 3253

Answers (2)

Adarsh Khalique
Adarsh Khalique

Reputation: 66

This will work 

select
    event.eventNumber
from
    event
order by
    cast(event.eventNumber as integer), event.eventNumber asc

The "cast(event.eventNumber as integer)" only sort the integers values.

The "cast(event.eventNumber as integer), event.eventNumber asc" sort both integer and string values

Upvotes: 3

Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

You can try this 

select cast(event.eventNumber as integer) from event order by event.eventNumber asc;

Upvotes: 1

Related Questions