SQL order by Issue

25 pts.
Tags:
Dynamic SQL
SELECT statement
SQL
SQL query optimization
Please help me write this. I need a SQL where the order by is done based on a variable passed to the query.SELECT * FROM (SELECT distinct gl.group_id, gl.group_name, gl.group_description, gl.status_code, gl.member_count, (SELECT grpp.group_name FROM test_group_relationship grel JOIN test_group grpp ON grel.parent_group_id = grpp.group_id WHERE grel.child_group_id = gl.group_id ) AS parent_group_name, gl.group_name_key, gl.group_description_key FROM test_group AS gl WHERE gl.group_org_id = '3909' AND (gl.group_name_key LIKE '%GROUP%') ) AS data_set ORDER BY DECODE(:sort_key, 'name', 'constant', 'description', group_description_key, 'memberCount', LPAD(member_count, 4), 'status', LPAD(status_code, 4), 'parentGroupName', parent_group_name )NULLS FIRST, UPPER(SUBSTR(group_name, 1, 1)), SUBSTR(group_name, 1, 1) DESC, UPPER(group_name), group_name DESC, group_name_key, ; When,sort_key :='name'.As we can see it evaluates to a constant and then the remaining columns in the ORDER BY clause. Now,when variable sort_key:= 'description' or 'memberCount'....each of the values, then the ORDER BY LOGIC is different for each of them. n brief,each of the options that the sort_key variable gets-the ORDER BY logic is different and i have to implement it in the same SQL only now.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The only way to do dynamic ordering is by using dynamic SQL. This is a very simple example.

<pre>CREATE PROCEDURE YourProc
@OrderBy VARCHAR(50)
AS
DECLARE @SQL VARCHAR(4000)
SET @SQL = ‘SELECT *
FROM YourTable
ORDER BY ‘ + @OrderBy

EXEC (@SQL)
GO</pre>

If you are using Oracle, you could execute the dynamic statement this way:

EXECUTE IMMEDIATE <string>;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    There is another way to do dynamic ordering. Create an additional result column to contain a sort code and populate that column with a string value based on the selection criteria, then use that column as the Order By column. This isn't hard, but requires some grunt work. It is easy enough to handle strings, but if you convert numeric values to strings they will sort oddly ("100" comes before "2"). For similar problems, my solution was to have functions that converted different numeric types to a string with leading zeros (within a reasonable range) so the resulting column sorted ok. For multi-column sorting, you would need to write a function that took the argument string and for each row of result data, returned a concatenated string of the field data in the selected order, like in the following: select ... , mySortFunction(order_arg, group_description_key, lpad(status_code, 4), to_char(num_field, 'pattern'), ...) as theSortColumn, ... where ... order by theSortColumn MRDenny's approach is much easier, but (particularly in older Oracles) often a compiled procedure query was far more efficient than running a dynamic sql query, so generating a sort column value was a lot less overhead. Note - This is only true when the result set of the query is substantially smaller than the source data (so you only call the function on a relatively small number of rows). If you are commonly returning a large portion of the source data, then generating the dynamic sql query is likely to be just as efficient.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following