Suppose that I have the following

table = List[{"ID", "Variable 1", "Variable 2"}, {"Alpha", 1, 
   0}, {"Beta", 1, 1}, {"Alpha", 0, 0}, {"Gamma", 1, 0}, {"Gamma", 2, 

Regard to this Post, we can use select (user-defined function) to select what we want based on either rows or columns. For example, we can use

select[table, where["ID" == "Alpha"]]

the return will be {{"Alpha", 1, 0}, {"Alpha", 0, 0}}. If I want to select multiple categories, for example, I want "Alpha" and "Gamma", I can use

select[table, where["ID" == "Alpha"||"ID" == "Gamma"]]

My point here is that you can do this if you have small table. What if you have very large table and you need to select multiple categories. You may have to write

select[table, where["ID" == "Alpha"||"ID" == "Gamma"||"ID"=="Beta"||....so on]]

What I did was to use

Flatten[select[table, where["ID"==#]]&/@{"Alpha", "Gamma"},1]

but it is slow if you have very large table.

Could anyone give me suggestions?


3 Answers 3


This is what you want (if you want to stay within the syntax of the linked post):

select[table, where[MemberQ[{"Alpha", "Beta"}, "ID"]]]

On a large table, this will be rather slow, yes. But not much slower than using explicit lists, because my solution you linked simply constructs a pure function at the start, and then everything would be the same as if you would've constructed it yourself.

If one wants to optimize some queries, one needs indexes, which I wasn't concerned with in that post. Generally, that post should not be viewed as a complete solution, but rather as an illustration that one can add friendly syntax rather easily.


Make your own, e.g.:

mySelect[col_, vals_] := 
 Select[table[[2 ;;]], 
  MemberQ[vals, #[[Position[table[[1]], col][[1, 1]]]]] &]

mySelect["ID", {"Beta", "Gamma"}]

(*  {{"Beta", 1, 1}, {"Gamma", 1, 0}, {"Gamma", 2, 2}}  *)

mySelect["Variable 1", {1, 2}]

(* {"Alpha", 1, 0}, {"Beta", 1, 1}, {"Gamma", 1, 0}, {"Gamma", 2, 2} *)

But honestly, better to just do things with native list capabilities vs a toy (no slight intended or implied) "SQL", or if you need/want SQL, use one with Mathematica's SQL interface capabilities.

  • 1
    $\begingroup$ Actually, I find SQL based constructs more natural. May be that is why LINQ is so popular in C#. en.wikipedia.org/wiki/Language_Integrated_Query LINQ extends the language by the addition of query expressions, which are akin to SQL statements, and can be used to conveniently extract and process data from arrays, enumerable classes, XML documents, relational databases, and third-party data sources. I do not see why Mathematica can't have LINQ added to it with an API on top it. $\endgroup$
    – Nasser
    Commented Mar 20, 2014 at 8:05
  • $\begingroup$ I don't agree with this part : "But honestly, better to just do things with native list capabilities vs a toy (no slight intended or implied) "SQL", or if you need/want SQL, use one with Mathematica's SQL interface capabilities" - the linked post of mine actually is about native lists, just adding a friendlier syntax to those. I believe that in just under something like 200 lines of code one can have a rather complete wrapper with most important SQL - like commands, and it will be much more light-weight than SQL connectivity, but much more user-friendly than using plain lists. $\endgroup$ Commented Mar 20, 2014 at 10:12

For this kind of task I use a helper function to create pure functions from expressions such as "ID" == "Alpha", replacing column labels like "ID" with their appropriate Slot. The resulting pure functions may then be used in Select, Map and Apply etc.

ColumnFunction::usage = "ColumnFunction[expr_, columns_List]; Constucts a pure function,
suitable for Apply or MapThread to use, taking in expr as the function expressed in
terms of column names and replacing them with the appropriate #n taken from their order 
in the list columns.";
ColumnFunction[expr_, columns_List] := ColumnFunction[expr, columns] =
    Module[ {rules},
      rules = Apply[Thread[# -> Range[Length[#]]]&, {columns}] /. i_Integer :> Slot[i];
      Sow[Apply[Function, Hold[expr] /. rules]];
      On[Part::"partw"];][[2, 1, 1]]
SetAttributes[ColumnFunction, HoldFirst];

Then use Select wrapped in this manner...

selectResultsBy::usage = StringJoin["Selects from the data given those rows for which
the given expression as passed through ColumnFunction with the first row of headedData
which matches the pattern ", headedDataForm, " returns True."];
SetAttributes[selectResultsBy, HoldRest];
selectResultsBy[headedData_List, expr_] := Module[{f},
  Apply[Apply[Function[{header, data},
    f = ColumnFunction[expr, header];
    Join[{header}, Select[data, Apply[f, #]&]]], {First[#], Rest[#]}]&, {headedData}]];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.