Introduction
I have explained in the previous article how to work with SharePoint list items, basically performing CRUD operations, using the combination of REST API and jQuery Ajax. The REST URI ends with any OData query operators to specify selecting, sorting, or filtering.Let’s see other parameters and options which can be used with REST services. We will start from basic REST url and will take Selecting, filtering, sorting and pagination options one by one.
The main agenda of this Article is to understand how you can take selecting, filtering, sorting and pagination options one by one.
Scenarios
There can be scenarios where you want to perform additional operations on data returned by REST API like selecting, filtering, sorting and paginationfields only etc. For such operations while forming Url to Get data, we have to add some parameters as described below:
- Selecting and sorting items
$select
This ‘’ /administration/_api/web/lists/getbytitle('infolist')/items’url returns all items with all possible fields or list columns. But what if list has more than 20-30 columns? It’s not good practice to get all fields. Get only specific fields which are required. You can select the specific attributes to return for your items using the $select parameter. Below example show how to use it.
Syntax for this is $select=Field1, Field2, Field3
/_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company
'$orderby'
If your response is a list of items, each with a number of field names, you can sort the list on a given field name using the $orderby, $orderby.asc or $orderby.desc system filter parameter.
The first two specify sorting in ascending order and the third one descending order. It's simple, you can use '$orderby' parameter and provide the field name. REST service will return sorted list items in response.
Syntax for this is $orderby=(Column Internal Name order)
See below examples
Ascending Order
- /_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company&$orderby= Employee asc
- /_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company&$orderby= Employee desc
Divesh GuptaPosted Apr 2, 2020, 9:21 AM
Hey Sagar, I am trying to use the Month(FieldName) in the query...the query is forming fine but it throws and error saying query not valid at this month(fieldname). the field used is not a calculated field but a general datetime fields
Karthick NPosted Jan 31, 2020, 5:08 AM
I have done select it is working fine. When I tried to filter I used below query it is not working. ?$filter=IsRequired eq Yes. I am getting error: $filter=IsRequired eq 2" is not valid. Kindly check and suggest.
Prathamesh DokePosted Sep 18, 2019, 9:16 AM
Https://dimensiondata3.sharepoint.com/_api/web/lists/getbytitle('PrathameshDemo')/items?$filter=Created eq datetime('13-09-2019') ------- how to fetch a list for a specific date. i am getting error 'the query is not valid'
Azad AliPosted Jul 16, 2019, 8:52 AM
How can latest images fist display or how can shorting
Gagas WalandriPosted Apr 1, 2019, 5:08 AM
I tried to retrieve data through workflow using Rest API filter but the responsecontent return empty even though the query return the data i wanted when i execute it through browser, do you have any idea or solution?
Gunmeet ChaudharyPosted Mar 27, 2019, 2:42 AM
Rest api vs jsom
Arvinth SugumaranPosted Nov 27, 2018, 4:03 AM
VerticalID VericalName DigitalIndex 1 Vertical1 82 2 Vertical2 71 3 Vertical3 82 Let me the REST CALL Select VerticalID ,VerticalName ,DigitalIndex where DigitalIndex = Select MAX(DigitalIndex) from List(DigitalIndex) MaxValues of Digitalndex is a Dynamic one Resulting Data : VerticalID VerticalName DigitalIndex 1 Vertical1 82 3 Vertical3 82
Arvinth SugumaranPosted Nov 22, 2018, 7:27 AM
Let me know to how to get the data from the list list select = Field1,Field2,Field3 &$Filter= Field4 eq (Select Field4 Top 1 OrderBy=Field4 desc)
Arvinth SugumaranPosted Nov 22, 2018, 6:35 AM
I want to retrived some data from list based on highest value from list cq/optimus/devdash/_api/lists/getbytitle('Verticals1')/items?select=VerticalId,VerticalName,DigitalIndex&$filter=DigitalIndex eq $top=1&$select=DigitalIndex&$orderby=DigitalIndex desc I provide the URI above how to use Sub Query
Arvinth SugumaranPosted Nov 22, 2018, 6:32 AM
/devdash/_api/lists/getbytitle('Verticals1')/items?select=VerticalId,VerticalName,DigitalIndex&$filter=DigitalIndex eq $top=1&$select=DigitalIndex&$orderby=DigitalIndex desc
Deepak JPosted Oct 9, 2018, 5:21 AM
How Can I do group by using oData Query
clover spdPosted Aug 27, 2018, 1:07 AM
How i filter list data for from date to to date within one column.Please reply me asap
Praveen KumarPosted Jul 26, 2018, 8:12 AM
Can you please suggest me a solution for it
Praveen KumarPosted Jul 26, 2018, 8:12 AM
Hai pavan i am having text box in my html, and submit button is given reference of a function which displays an alert, but i am getting errors like "_Sp.context undefined" error.
Pavan NesargiPosted Jul 18, 2018, 6:02 AM
How to fetch top 5 then next 5 items ?
Praveen KumarPosted Jul 5, 2018, 9:05 AM
How to fetch the top 5 records of the list using filter. Is it possible?
Ramakrishna ChandragiriPosted Feb 27, 2018, 7:58 AM
Unable to filter the data on SharePoint 2016 using the following query filter $filter=startswith(FileDirRef, '/sites/docenter/Documents/TestFolder'). Any thoughts?
Ramana KvPosted Nov 13, 2017, 1:33 PM
Very nice article and helped me a lot to create external lists in my SharePoint Online site. However, How can I set the datasource filters dynamically in rest call to get filtered values?
Gowtham RajamanickamPosted Apr 2, 2016, 12:33 PM
Good one..
Humayun Kabir MamunPosted Mar 23, 2016, 5:17 AM
Nice...
Vignesh ManiPosted Mar 22, 2016, 9:14 AM
nice
Sibeesh VenuPosted Mar 22, 2016, 6:39 AM
Nice Share
Mohammed IbrahimPosted Mar 21, 2016, 10:23 AM
nice