--- agentTools: projectIndex: https://docs.thinknum.com/llms.txt --- # Sort [block:api-header] { "title": "Sorting the Results of a Query" } [/block] The result of your query can be sorted. Columns that are sortable include the columns of the original dataset and the columns generated as a result of the query (such as custom aggregations). Sorting by multiple columns is also supported. Sorting has the following keys: [block:parameters] { "data": { "h-0": "Key", "h-1": "Description", "0-0": "column", "0-1": "Name of the column in the query result on which the result needs be sorted", "1-0": "order", "1-1": "`asc` if the result should be sorted in ascending order. `desc` if the result should be sorted in descending order" }, "cols": 2, "rows": 2 } [/block] For example, to return a count of the number of Apple stores by city and state with state sorted alphabetically followed by the city with the most stores, a sample query would be: [block:code] { "codes": [ { "code": "POST /dataset/store/query/ HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\n\n{\n \"tickers\": [\n \"nasdaq:aapl\"\n ],\n \"groups\": [\n {\n \"column\": \"state\"\n },\n {\n \"column\": \"city\"\n }\n ],\n \"aggregations\": [\n {\n \"column\": \"city\",\n \"type\": \"count\"\n }\n ],\n \"sorts\": [\n {\n \"column\": \"city@count\",\n \"order\": \"desc\"\n },\n {\n \"column\": \"city\",\n \"order\": \"asc\"\n }\n ]\n}", "language": "http" }, { "code": "curl -X POST \"https://data.thinknum.com/datasets/store/query/\" \\\n-H \"Accept: application/json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\" \\\n-d $'{\n \"tickers\": [\n \"nasdaq:aapl\"\n ],\n \"groups\": [\n {\n \"column\": \"state\"\n },\n {\n \"column\": \"city\"\n }\n ],\n \"aggregations\": [\n {\n \"column\": \"city\",\n \"type\": \"count\"\n }\n ],\n \"sorts\": [\n {\n \"column\": \"city@count\",\n \"order\": \"desc\"\n },\n {\n \"column\": \"city\",\n \"order\": \"asc\"\n }\n ]\n}'\n", "language": "curl" }, { "code": "response = requests.post(\n url='https://data.thinknum.com/datasets/store/query/', \n headers={\n 'Authorization': 'token 01234567890123456789', \n 'X-API-Version': '20151130', \n 'Accept': 'application/json'\n }, \n data=json.dumps({\n \"tickers\": [\n \"nasdaq:aapl\"\n ],\n \"groups\": [\n {\n \"column\": \"state\"\n },\n {\n \"column\": \"city\"\n }\n ],\n \"aggregations\": [\n {\n \"column\": \"city\",\n \"type\": \"count\"\n }\n ],\n \"sorts\": [\n {\n \"column\": \"city@count\",\n \"order\": \"desc\"\n },\n {\n \"column\": \"city\",\n \"order\": \"asc\"\n }\n ]\n })\n)\nresults = json.loads(response.text)", "language": "python" } ] } [/block] A sample response follows: [block:code] { "codes": [ { "code": "{\n \"state\": \"running\",\n \"total\": 0,\n \"id\": \"c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c\",\n \"formats\": [\n \"application/vnd.thinknum.table+json\",\n \"application/vnd.thinknum.chart+json\"\n ]\n}", "language": "json" } ] } [/block] To check if query is completed: [block:code] { "codes": [ { "code": "HEAD /datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\nAccept: application/vnd.thinknum.table+json", "language": "http" }, { "code": "curl -I HEAD \"https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c\" \\\n-H \"Accept: application/vnd.thinknum.table+json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\"", "language": "curl" }, { "code": "response = requests.head(\n url='https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c', \n headers={\n 'Authorization': 'token 01234567890123456789', \n 'X-API-Version': '20151130', \n 'Accept': 'application/vnd.thinknum.table+json'\n }\n)\nresults = dict(response.headers)", "language": "python" } ] } [/block] A sample response follows: [block:code] { "codes": [ { "code": "HTTP/1.1 200 OK\nServer: nginx\nDate: Wed, 28 Apr 2021 15:55:31 GMT\nContent-Type: application/json\nConnection: keep-alive\nX-Truncated: false\nX-Formats: application/vnd.thinknum.table+json, application/vnd.thinknum.chart+json, text/csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\nX-Total: 6903\nX-State: complete\nStrict-Transport-Security: max-age=15768000", "language": "json" } ] } [/block] To retrieve data: [block:code] { "codes": [ { "code": "GET /datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c?limit=10&start=1 HTTP/1.1\nAuthorization: token 01234567890123456789\nX-API-Version: 20151130\nAccept: application/vnd.thinknum.table+json\nContent-Type: application/json", "language": "http" }, { "code": "curl -X GET \"https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c?limit=10&start=1\" \\\n-H \"Accept: application/vnd.thinknum.table+json\" \\\n-H \"Content-Type: application/json\" \\\n-H \"X-API-Version: 20151130\" \\\n-H \"Authorization: token 01234567890123456789\"", "language": "curl" }, { "code": "response = requests.get(\n url='https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c', \n headers={\n 'Authorization': 'token 01234567890123456789', \n 'X-API-Version': '20151130', \n 'Accept': 'application/vnd.thinknum.table+json'\n }\n)\nresults = json.loads(response.text)", "language": "python" } ] } [/block] A sample response follows: [block:code] { "codes": [ { "code": "{\n \"group_fields\": [\n ...\n ],\n \"total\": 6903,\n \"id\": \"c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c\",\n \"count\": 10,\n \"rows\": [\n [\n \"SAN ANTONIO\",\n \"TX\",\n 357\n ],\n [\n \"LAS VEGAS\",\n \"NV\",\n 298\n ],\n [\n \"MIAMI\",\n \"FL\",\n 271\n ],\n [\n \"JACKSONVILLE\",\n \"FL\",\n 228\n ],\n [\n \"EL PASO\",\n \"TX\",\n 225\n ],\n [\n \"TUCSON\",\n \"AZ\",\n 215\n ],\n [\n \"AUSTIN\",\n \"TX\",\n 207\n ],\n [\n \"HOUSTON\",\n \"TX\",\n 200\n ],\n [\n \"INDIANAPOLIS\",\n \"IN\",\n 180\n ],\n [\n \"CINCINNATI\",\n \"OH\",\n 177\n ]\n ],\n \"fields\": [\n {\n \"display_name\": \"City\",\n \"format\": null,\n \"metric\": false,\n \"id\": \"city\",\n \"length\": 64,\n \"summary\": \"\",\n \"type\": \"string\",\n \"options\": []\n },\n {\n \"display_name\": \"State\",\n \"format\": null,\n \"metric\": false,\n \"id\": \"state\",\n \"length\": 64,\n \"summary\": \"\",\n \"type\": \"string\",\n \"options\": []\n },\n {\n \"display_name\": \"City (Count)\",\n \"format\": null,\n \"metric\": true,\n \"id\": \"city@count\",\n \"length\": null,\n \"summary\": \"\",\n \"type\": \"number\",\n \"options\": []\n }\n ],\n \"sort_fields\": [\n {\n \"column\": \"city@count\",\n \"order\": \"desc\"\n },\n {\n \"column\": \"city\",\n \"order\": \"asc\"\n }\n ],\n \"start\": 1,\n \"state\": \"complete\",\n \"limit\": 10,\n \"last_date_updated\": \"2021-04-01T02:34:56Z\"\n}", "language": "json" } ] } [/block]