Sorting in JPQL

Sorting results in JPQL is straightforward and works very similarly to standard SQL, using the ORDER BY clause. It allows you to organize your queried entities based on their field values.

Suppose you have the following Employee data in your database:

Employee
---------
Amit    50000
Rahul   70000
John    40000

Ascending Order (ASC)

To sort the employees by salary from lowest to highest, you use ASC (which is also the default if you omit the direction):

SELECT e
FROM Employee e
ORDER BY e.salary ASC

Result:

John    (40000)
Amit    (50000)
Rahul   (70000)

Descending Order (DESC)

To sort the employees by salary from highest to lowest, you use DESC:

SELECT e
FROM Employee e
ORDER BY e.salary DESC

Result:

Rahul   (70000)
Amit    (50000)
John    (40000)

30. Sorting with Multiple Fields

Often, sorting by a single field isn't enough. You might want to group data together (like by department) and then sort within those groups. You can achieve this by specifying multiple fields separated by commas.

Example:

SELECT e
FROM Employee e
ORDER BY e.department ASC, e.salary DESC

Meaning:

• First priority: Sort alphabetically by department ASC.
• Second priority: Within the same department, sort by salary DESC.

This is extremely important because the ORDER BY clause is evaluated left to right in terms of sort keys. The first field dictates the primary grouping, the second dictates the order within that group, and so on.

Interactive Demo: Single and Multi-Column Sorting

Sorting is easier to understand when you can see it in action. Try clicking the column headers in the table below to simulate how JPQL's ORDER BY applies ascending (ASC) and descending (DESC) logic. (Hold Shift while clicking to apply a secondary sort, simulating multi-field sorting like ORDER BY department ASC, salary DESC).

Idea: An interactive data table that allows users to click headers to sort single columns (ASC/DESC) and shift-click to demonstrate multi-column sorting priorities. Visual type: Interactive Sortable Data Table Data specification: - Data structure: Tabular dataset with Name (String), Department (String), and Salary (Number). - Initial values: [ {"Name": "Amit", "Department": "IT", "Salary": 50000}, {"Name": "Rahul", "Department": "HR", "Salary": 70000}, {"Name": "John", "Department": "IT", "Salary": 40000}, {"Name": "Priya", "Department": "Sales", "Salary": 60000}, {"Name": "Anita", "Department": "HR", "Salary": 45000}, {"Name": "Vikram", "Department": "IT", "Salary": 90000} ] - Mapping: Columns map directly to Name, Department, and Salary. Sort icons (arrows) appear next to headers when clicked. User controls: Clickable table headers. Interactivity: - Click a header: Sorts that column ASC. Click again: Sorts DESC. - Multi-sort: Visual indication of primary (1) and secondary (2) sort columns when multiple fields are used. Animation: Rows smoothly reorder when headers are clicked.

T3 Interview Summary

If the interviewer asks: "How does multiple-field sorting work in JPQL?"

"In JPQL, you can use the ORDER BY clause with multiple entity fields separated by commas (e.g., ORDER BY e.department ASC, e.salary DESC). The database evaluates these sort keys from left to right. It will first group and sort the result set based on the first field (department). If there are ties in the first field, it will then look at the second field (salary) to determine the final order within that specific group."