StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This can be done in one query if you use analytic queries, but I would personally use temporary tables and several queries.</p> <pre><code>CREATE TEMPORARY TABLE _ResourceDetail AS SELECT ResourceID , Company , Division , Section , Permitted , CASE WHEN Company IS NULL THEN 0 ELSE 1 END + CASE WHEN Division IS NULL THEN 0 ELSE 2 END + CASE WHEN Section IS NULL THEN 0 ELSE 4 END AS Priority FROM ResourcePermissions WHERE (Company = 'Company 1' OR Company IS NULL) AND (Division = 'Division A' OR Division IS NULL) AND (Section = 'Blue Section' OR Section IS NULL); CREATE TEMPORARY TABLE _BestResource AS SELECT ResourceID, max(Priority) as MaxPriority FROM _ResourceDetail GROUP BY ResourceID; SELECT d.ResourceID , d.Company , d.Division , d.Section , d.Permitted FROM _ResourceDetail d JOIN _BestResource b ON d.ResourceID = b.ResourceID AND d.Priority = b.MaxPriority ORDER BY d.ResourceID; </code></pre> <p>Alternately you can easily just put an <code>ORDER BY</code> on the first query, and trivially filter for the maximum priority in a loop. (Or even push the calculation of <code>Priority</code> out of the database.)</p> <p>Going the other way you can learn about analytic queries, and can use the first query as an input to a second query that labels the permissions for a recourse according to highest priority first, that feeds into a third query that selects just the highest priority. That will push work to the database, but I tend to find that approach less readable.</p> <p>Incidentally it is worth noting that if <code>ResourcePermissions</code> gets large, your query as it stands will not be a good candidate for taking advantage of indexes. The 8 query version might therefore run significantly faster.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload