Hello
I want to work with arrays/tables, i.e. to create tables based on the main table after filtering the values in values combined ways.
In DAX or EXCEL, this is very nicely done with FILTER and SORT and then you can use MIN/MAX/RANKX to get the right values from the resulted array.
Is there something similar in SQL?
I think it gets quite messy if you have to run separate or subquery SELECT statements, store them in variables, etc.
What is the neatest practice?
Thanks!
No - SQL does not have an array (unless you consider a table an array). A SQL statement is really describing a 'table' - therefore it can also be considered a virtual table (or array).
You don't have to run separate sub-queries and store them in a variable. You can create a view - which is just a stored query that is then included in the query calling that view. You can also use common-table expressions which are like inline-views - or derived tables (same as CTE's).
If performance becomes an issue - then you can select the data into a temp table, then query against the select table.
All FILTER and SORT do in DAX/EXCEL is hide the implementation of a derived table, but the concept is the same. You can do the same thing with views...