13
Multisort in Sheets, SQL, Python, and JavaScript
Sorting a 2D table is straightforward in most software solutions designed for data processing. If you are like me, sorting by single column was easy to remember after discovering the feature for the first time. But sorting by multiple columns can be a headache sometimes, so here are my recipes for some of the most popular tools.
Let's imagine that following table represents some useful data. We want to sort our records by column A (ascending) first, then for all records with the same value of A, we want to sort those sub-groups by column B (descending):
A↓ B C A↓ B↑ C
1 1 a 1 3 c
1 2 b 1 2 b
1 3 c -----> 1 1 a
2 1 d 2 3 f
2 2 e 2 2 e
2 3 f 2 1 dWe can sort by multiple columns in Microsoft Excel by using
Data -> Sort dialog in the ribbon, or in Google Sheets by using Data -> Sort range in the menu (presumably, OpenOffice Calc and other tools have something similar):
Comma separated list of column name expressions is supported in the
ORDER BY clause at least since SQL:1999 (from what I could see in The History of SQL Standards). And it's supported by all major implementations (even SQLite). You can try the following example on SQL Fiddle:select *
from myData
order by A, B descPractical (or lazy) people like me use pandas to organize all their Python data that need multi-column sort, just like in this Replit example:
df.sort_values(by=['A', 'B'], ascending=[True, False])While there might be some nice data libraries in the JavaScript world too, let's imagine we receive a JSON data from some API that is an array of objects like
[{A: 1, B: 1, C: 'a'}, ...] and that we don't want to compare all suitable libraries for features, bundle size, and performance. Let's say we want a nice little utility function that accepts some array and SQL-like order criteria, then returns a sorted array, like following:sorted(jsonLikeData, [{A: 'asc'}, {B: 'desc'}])Is this even possible? Easy to do? That's what we asked ourselves with Artem while he was implementing PR#103 in mswjs/data.
Here is a simplified version that you can try in TypeScript Playground:
function sorted(data, orderBy) {
return [...data].sort((a, b) => {
for (const criteria of orderBy) {
const [key, value] = Object.entries(criteria)[0]
const isAsc = value === 'asc'
if (a[key] > b[key]) {
return isAsc ? 1 : -1
}
if (a[key] < b[key]) {
return isAsc ? -1 : 1
}
// else, if they are equal, continue to next criteria
}
// if not returned yet, a and b are equal by all criteria
return 0
})
}If you are not familiar with JavaScript Array.sort, it is an in-place method that takes a function as an argument. It calls your comparison function multiple times, each time with 2 elements from the array. It expects a return value of
-1 if the first element should be sorted before the second, 1 if the first element belongs after the second, and 0 if the pre-existing sort order should be preserved (which is needed for a "stable sort"). So we need to iterate over all criteria and return a value as soon as we can determine it... We would need to handle a few more edge cases in practice, but that's all for the core idea how we can approach the multisort implementation in JavaScript.See you next time, when I will talk about joining data sets in Sheets, SQL, Python and JavaScript.