Sorting the Results of a Query

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:

Key

Description

column

Name of the column in the query result on which the result needs be sorted

order

asc if the result should be sorted in ascending order. desc if the result should be sorted in descending order

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:

POST /dataset/store/query/ HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130

{
    "tickers": [
        "nasdaq:aapl"
    ],
    "groups": [
        {
            "column": "state"
        },
        {
            "column": "city"
        }
    ],
    "aggregations": [
        {
            "column": "city",
            "type": "count"
        }
    ],
    "sorts": [
        {
            "column": "[email protected]",
            "order": "desc"
        },
        {
            "column": "city",
            "order": "asc"
        }
    ]
}
curl -X POST "https://data.thinknum.com/datasets/store/query/" \
-H "Accept: application/json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789" \
-d $'{
    "tickers": [
        "nasdaq:aapl"
    ],
    "groups": [
        {
            "column": "state"
        },
        {
            "column": "city"
        }
    ],
    "aggregations": [
        {
            "column": "city",
            "type": "count"
        }
    ],
    "sorts": [
        {
            "column": "[email protected]",
            "order": "desc"
        },
        {
            "column": "city",
            "order": "asc"
        }
    ]
}'
response = requests.post(
    url='https://data.thinknum.com/datasets/store/query/', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/json'
    }, 
    data=json.dumps({
        "tickers": [
            "nasdaq:aapl"
        ],
        "groups": [
            {
                "column": "state"
            },
            {
                "column": "city"
            }
        ],
        "aggregations": [
            {
                "column": "city",
                "type": "count"
            }
        ],
        "sorts": [
            {
                "column": "[email protected]",
                "order": "desc"
            },
            {
                "column": "city",
                "order": "asc"
            }
        ]
    })
)
results = json.loads(response.text)

A sample response follows:

{
    "state": "running",
    "total": 0,
    "id": "c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c",
    "formats": [
        "application/vnd.thinknum.table+json",
        "application/vnd.thinknum.chart+json"
    ]
}

To check if query is completed:

HEAD /datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130
Accept: application/vnd.thinknum.table+json
curl -I HEAD "https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c" \
-H "Accept: application/vnd.thinknum.table+json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789"
response = requests.head(
    url='https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/vnd.thinknum.table+json'
    }
)
results = dict(response.headers)

A sample response follows:

HTTP/1.1 200 OK
Server: nginx
Date: Wed, 28 Apr 2021 15:55:31 GMT
Content-Type: application/json
Connection: keep-alive
X-Truncated: false
X-Formats: application/vnd.thinknum.table+json, application/vnd.thinknum.chart+json, text/csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
X-Total: 6903
X-State: complete
Strict-Transport-Security: max-age=15768000

To retrieve data:

GET /datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c?limit=10&start=1 HTTP/1.1
Authorization: token 01234567890123456789
X-API-Version: 20151130
Accept: application/vnd.thinknum.table+json
Content-Type: application/json
curl -X GET "https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c?limit=10&start=1" \
-H "Accept: application/vnd.thinknum.table+json" \
-H "Content-Type: application/json" \
-H "X-API-Version: 20151130" \
-H "Authorization: token 01234567890123456789"
response = requests.get(
    url='https://data.thinknum.com/datasets/store/query/c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c', 
    headers={
        'Authorization': 'token 01234567890123456789', 
        'X-API-Version': '20151130', 
        'Accept': 'application/vnd.thinknum.table+json'
    }
)
results = json.loads(response.text)

A sample response follows:

{
    "group_fields": [
        ...
    ],
    "total": 6903,
    "id": "c6403ead804be9f7e48d237cd8c8d97d63195b583016ccef60f5157215fbd47c",
    "count": 10,
    "rows": [
        [
            "SAN ANTONIO",
            "TX",
            357
        ],
        [
            "LAS VEGAS",
            "NV",
            298
        ],
        [
            "MIAMI",
            "FL",
            271
        ],
        [
            "JACKSONVILLE",
            "FL",
            228
        ],
        [
            "EL PASO",
            "TX",
            225
        ],
        [
            "TUCSON",
            "AZ",
            215
        ],
        [
            "AUSTIN",
            "TX",
            207
        ],
        [
            "HOUSTON",
            "TX",
            200
        ],
        [
            "INDIANAPOLIS",
            "IN",
            180
        ],
        [
            "CINCINNATI",
            "OH",
            177
        ]
    ],
    "fields": [
        {
            "display_name": "City",
            "format": null,
            "metric": false,
            "id": "city",
            "length": 64,
            "summary": "",
            "type": "string",
            "options": []
        },
        {
            "display_name": "State",
            "format": null,
            "metric": false,
            "id": "state",
            "length": 64,
            "summary": "",
            "type": "string",
            "options": []
        },
        {
            "display_name": "City (Count)",
            "format": null,
            "metric": true,
            "id": "[email protected]",
            "length": null,
            "summary": "",
            "type": "number",
            "options": []
        }
    ],
    "sort_fields": [
        {
            "column": "[email protected]",
            "order": "desc"
        },
        {
            "column": "city",
            "order": "asc"
        }
    ],
    "start": 1,
    "state": "complete",
    "limit": 10,
    "last_date_updated": "2021-04-01T02:34:56Z"
}