T-SQL ORDER BY according to a condition

Question

I am writing some sort of resources management system.

A resource is an instance of a definition. A definition is the metadata, basically it contains the properties.

This is in general my DB:

TypeDefinition
id    name
===============
1     CPU


PropertyDefinition
id    name       typeDefinitionId   valueType
================================================
1     frequency  1                  int
2     status     1                  string


TypeInstance
id    name     typeDefinitionId
=================================
1     CPU#1    1  
2     CPU#2    1


PropertyInstanceValue
id   propertyDefinitionId  typeInstanceId   valueType   intValue  StringValue FloatValue
========================================================================================
1    1                     1                int         10
2    2                     1                string                Pending
3    1                     2                int         20
4    2                     2                string                Approved

REQUIREMENT:

order all resources according to a specific property value.

For example: order all resources according to their status --> Meaning CPU#2 will appear before CPU#1 because “Approved” is before “Pending”.

If we were to order according to frequency, CPU#1 will appear before CPU#2 because 10 is before 20.

So I need to sort each time according to a different column (intValue / stringValue/ FloatValue / etc), depending on the property's valueType.

Any suggestion?

LIMITATION:

PIVOT is currently the only option we've thought of, but it's not really possible since the DB is huge and I need the query to be as fast as possible.

Thanks a lot in advance,

Michal.

Answer 1

If the problem is that you don't want to dynamically build the query then use this order by structure:

order by case @orderby
    when 'status' then status
    when 'frequency' then frequency
    end
option (recompile)

You will pass the @orderby parameter. The final recompile option is to force the engine to build a new plan according to the passed parameters, that is, assuming you are using a stored procedure.