Sort

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": "city@count",
            "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": "city@count",
            "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": "city@count",
                "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": "city@count",
            "length": null,
            "summary": "",
            "type": "number",
            "options": []
        }
    ],
    "sort_fields": [
        {
            "column": "city@count",
            "order": "desc"
        },
        {
            "column": "city",
            "order": "asc"
        }
    ],
    "start": 1,
    "state": "complete",
    "limit": 10,
    "last_date_updated": "2021-04-01T02:34:56Z"
}