Order By Examples
| AVAILABLE API VERSION |
|---|
| API v59.0 and later |
You can use aggregate functions with or without an orderBy argument.
Example: Query Aggregate Fields with Ordering
This example queries the Industry field and an aggregate count function on Name, grouped by Industry. Use the orderBy argument for the aggregated Name field.
The result lists all industries and the number of accounts that are assigned to that particular industry.
By default, null values are returned first the results. To remove accounts without a specified industry, use where: { Industry: { ne: null } }.
All aggregate functions ignore null values, except for COUNT() and COUNT(Id).
Note
1query IndustryAndNameCount {
2 uiapi {
3 aggregate {
4 Account ( where: { Industry: { ne: null } },
5 groupBy: { Industry: { group: true } },
6 orderBy: { Name: { order: DESC, function: COUNT }}){
7 edges {
8 node {
9 aggregate {
10 Industry {
11 value
12 }
13 Name {
14 count { value }
15 }
16 }
17 }
18 }
19 }
20 }
21 }
22}Example Response for Aggregate Fields with Ordering
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "value": "Energy"
12 },
13 "Name": {
14 "count": {
15 "value": 3
16 }
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "Industry": {
25 "value": "Biotechnology"
26 },
27 "Name": {
28 "count": {
29 "value": 1
30 }
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "Industry": {
39 "value": "Construction"
40 },
41 "Name": {
42 "count": {
43 "value": 1
44 }
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "Industry": {
53 "value": "Consulting"
54 },
55 "Name": {
56 "count": {
57 "value": 1
58 }
59 }
60 }
61 }
62 },
63 {
64 "node": {
65 "aggregate": {
66 "Industry": {
67 "value": "Education"
68 },
69 "Name": {
70 "count": {
71 "value": 1
72 }
73 }
74 }
75 }
76 }
77 ]
78 }
79 }
80 }
81 },
82 "errors": []
83}The previous query is similar to this SOQL statement.
1SELECT Industry, COUNT(Name)
2FROM Account
3WHERE Industry != null
4GROUP BY Industry
5ORDER BY COUNT(Name) DESCSimilar to SOQL, you can’t order by the aggregated name count unless you also group by Industry. For example, you get an error if you don’t provide GROUP BY Industry.
You also can’t group by the aggregated name count. Only date aggregate functions are allowed in a grouping.
Example: Query Multiple Aggregate Fields with Ordering
This example queries the Industry field with 2 aggregate functions on AnnualRevenue and Name fields. Use the orderBy argument for the aggregated fields.
The result lists all industries, the corresponding average amount on the AnnualRevenue fields, and the number of accounts that are assigned to that particular industry.
1query IndustryAndAvgRevenue {
2 uiapi {
3 aggregate {
4 Account ( orderBy: {
5 Name: { order: ASC, function: COUNT },
6 AnnualRevenue: { order: ASC, function: AVG }
7 },
8 groupBy: { Industry: { group: true } } ) {
9 edges {
10 node {
11 aggregate {
12 Industry { value }
13 AnnualRevenue {
14 avg { value }
15 }
16 Name {
17 count { value }
18 }
19 }
20 }
21 }
22 }
23 }
24 }
25}Example Response for Multiple Aggregate Fields with Ordering
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "value": "Education"
12 },
13 "AnnualRevenue": {
14 "avg": {
15 "value": null
16 }
17 },
18 "Name": {
19 "count": {
20 "value": 1
21 }
22 }
23 }
24 }
25 },
26 {
27 "node": {
28 "aggregate": {
29 "Industry": {
30 "value": "Biotechnology"
31 },
32 "AnnualRevenue": {
33 "avg": {
34 "value": 30000000
35 }
36 },
37 "Name": {
38 "count": {
39 "value": 1
40 }
41 }
42 }
43 }
44 },
45 {
46 "node": {
47 "aggregate": {
48 "Industry": {
49 "value": "Consulting"
50 },
51 "AnnualRevenue": {
52 "avg": {
53 "value": 50000000
54 }
55 },
56 "Name": {
57 "count": {
58 "value": 1
59 }
60 }
61 }
62 }
63 },
64 {
65 "node": {
66 "aggregate": {
67 "Industry": {
68 "value": "Electronics"
69 },
70 "AnnualRevenue": {
71 "avg": {
72 "value": 139000000
73 }
74 },
75 "Name": {
76 "count": {
77 "value": 1
78 }
79 }
80 }
81 }
82 },
83 {
84 "node": {
85 "aggregate": {
86 "Industry": {
87 "value": "Apparel"
88 },
89 "AnnualRevenue": {
90 "avg": {
91 "value": 350000000
92 }
93 },
94 "Name": {
95 "count": {
96 "value": 1
97 }
98 }
99 }
100 }
101 },
102 {
103 "node": {
104 "aggregate": {
105 "Industry": {
106 "value": "Hospitality"
107 },
108 "AnnualRevenue": {
109 "avg": {
110 "value": 500000000
111 }
112 },
113 "Name": {
114 "count": {
115 "value": 1
116 }
117 }
118 }
119 }
120 },
121 {
122 "node": {
123 "aggregate": {
124 "Industry": {
125 "value": "Construction"
126 },
127 "AnnualRevenue": {
128 "avg": {
129 "value": 950000000
130 }
131 },
132 "Name": {
133 "count": {
134 "value": 1
135 }
136 }
137 }
138 }
139 },
140 {
141 "node": {
142 "aggregate": {
143 "Industry": {
144 "value": "Transportation"
145 },
146 "AnnualRevenue": {
147 "avg": {
148 "value": 950000000
149 }
150 },
151 "Name": {
152 "count": {
153 "value": 1
154 }
155 }
156 }
157 }
158 },
159 {
160 "node": {
161 "aggregate": {
162 "Industry": {
163 "value": "Energy"
164 },
165 "AnnualRevenue": {
166 "avg": {
167 "value": 5600000000
168 }
169 },
170 "Name": {
171 "count": {
172 "value": 3
173 }
174 }
175 }
176 }
177 },
178 {
179 "node": {
180 "aggregate": {
181 "Industry": {
182 "value": null
183 },
184 "AnnualRevenue": {
185 "avg": {
186 "value": null
187 }
188 },
189 "Name": {
190 "count": {
191 "value": 18
192 }
193 }
194 }
195 }
196 }
197 ]
198 }
199 }
200 }
201 },
202 "errors": []
203}The previous query is similar to this SOQL statement.
1SELECT Industry, AVG(AnnualRevenue), COUNT(Name)
2FROM Account
3GROUP BY Industry
4ORDER BY COUNT(Name), AVG(AnnualRevenue)Example: Query Minimum Revenue of All Industries with Ordering
To return the minimum value of a field, use the min function. This example returns the minimum value of the AnnualRevenue field for each industry.
1query MinRevenueGroupByIndustry {
2 uiapi {
3 aggregate {
4 Account (groupBy: { Industry: { group: true } }, orderBy: { AnnualRevenue: { function: MIN } } ) {
5 edges {
6 node {
7 aggregate {
8 AnnualRevenue { min { displayValue } }
9 Industry { value }
10 }
11 }
12 }
13 }
14 }
15 }
16}The orderBy argument orders the results in ascending order of annual revenue. Similar to SOQL, you can’t order by the aggregated minimum annual revenue unless you also group by Industry. In other words, if you use orderBy in a query, you must also include the groupBy argument.
Example Response for Minimum Revenue of All Industries
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "AnnualRevenue": {
11 "min": {
12 "displayValue": "$30,000,000"
13 }
14 },
15 "Industry": {
16 "value": "Biotechnology"
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "AnnualRevenue": {
25 "min": {
26 "displayValue": "$50,000,000"
27 }
28 },
29 "Industry": {
30 "value": "Consulting"
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "AnnualRevenue": {
39 "min": {
40 "displayValue": "$139,000,000"
41 }
42 },
43 "Industry": {
44 "value": "Electronics"
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "AnnualRevenue": {
53 "min": {
54 "displayValue": "$350,000,000"
55 }
56 },
57 "Industry": {
58 "value": "Apparel"
59 }
60 }
61 }
62 },
63 {
64 "node": {
65 "aggregate": {
66 "AnnualRevenue": {
67 "min": {
68 "displayValue": "$500,000,000"
69 }
70 },
71 "Industry": {
72 "value": "Hospitality"
73 }
74 }
75 }
76 },
77 {
78 "node": {
79 "aggregate": {
80 "AnnualRevenue": {
81 "min": {
82 "displayValue": "$5,600,000,000"
83 }
84 },
85 "Industry": {
86 "value": "Energy"
87 }
88 }
89 }
90 }
91 ]
92 }
93 }
94 }
95 },
96 "errors": []
97}The previous query is similar to this SOQL statement.
1SELECT MIN(AnnualRevenue), Industry
2FROM Account
3GROUP BY Industry
4ORDER BY MIN(AnnualRevenue)See Also
SOQL and SOSL Reference: ORDER BY
j