SQL order by Issue
Home > IT Answers > Development > SQL order by Issue
Rohit3312
25 pts.
0
Q:
SQL order by Issue
SQL, SELECT statement, SQL query optimization, Dynamic SQL
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.
ASKED: May 2 2009  4:44 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29855 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
The only way to do dynamic ordering is by using dynamic SQL. This is a very simple example.

CREATE PROCEDURE YourProc 
    @OrderBy VARCHAR(50)
 AS
 DECLARE @SQL VARCHAR(4000)
 SET @SQL = 'SELECT *
 FROM YourTable
 ORDER BY ' + @OrderBy
 
 EXEC (@SQL)
 GO


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

EXECUTE IMMEDIATE <string>;
Last Answered: May 4 2009  2:45 PM GMT by Carlosdl   29855 pts.
Latest Contributors: Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Kccrosser   1850 pts.  |   May 4 2009  8:29PM GMT

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.

 
0