Sorting in SQL Functions

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have function which returns a table.

HTML

Id      Name    Total   Status         
1	Kitu	565	SC
2	Kavya	576	FC
3	rajni	98	07/08/2011
4	devi	49	07/05/2010
5	laya	637	FC
6	gita	32	09/08/2011
7	guru	981	FCD
8	dev	63	07/05/2012
9	magi	45	09/10/2011
10	malya	562	SC
11	Kavi	56	09/10/2011
12	raki	89	SC
13	Rana	56	18/04/2011
14	rajiv	56	01/04/2011
15	ravi	56	09/09/2011
16	surya	56	26/04/2011
17	dev	56	06/05/2011
18	dina	56	12/1/2011

SQL

SELECT  * FROM [dbo].[fn_get_Student]() ORDER BY 
CASE
    WHEN ISDATE( [Status] ) = 1 THEN CONVERT( VARCHAR(50), CONVERT( DATETIME, [Status] ), 112 )
    ELSE [Status]
END

Result:

HTML

Id      Name    Total   Status
13	Rana	56	18/04/2011
4	devi	49	07/05/2010
14	rajiv	56	01/04/2011
17	dev	56	06/05/2011
3	rajni	98	07/08/2011
6	gita	32	09/08/2011
15	ravi	56	09/09/2011
9	magi	45	09/10/2011
11	Kavi	56	09/10/2011
18	dina	56	12/1/2011
8	dev	63	07/05/2012
16	surya	56	26/04/2011
5	laya	637	FC
2	Kavya	576	FC
7	guru	981	FCD
1	Kitu	565	SC
12	raki	89	SC
10	malya	562	SC

Status Column Consists of both date and nvarchar values. As you can see the date of status column, which is not sorted in correct format.

Is there any other approch to achive the task?

Posted 6-Sep-11 1:04am

swathi.N

Updated 13-Sep-11 1:49am

v5

Add a Solution

Comments

Wendelius 9-Sep-11 7:17am

Now status contains dates but also some text data. How should this be sorted? Dates first and then others or something else?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-09-06T01:19:00

Columns should always sort nicely even if the results come from a function. However a possible problem could be that the datatype of the returning column is wrong, for example string when double should be used etc.

If that's not the case, please post an example of the data and how it's sorted (wrong results)

Costica U · Answer 2 · 2011-09-10T02:55:00

This query shows how can sort using case statements. I hope this helps.

SQL

SET DATEFORMAT DMY

IF OBJECT_ID( 'tempdb..#TEMP' ) IS NOT NULL
    BEGIN
    DROP TABLE #TEMP
    END
CREATE TABLE #TEMP
    (
    [Id]        INT,
    [Name]      VARCHAR(50),
    [Total]     INT,
    [Status]    VARCHAR(50)
    
    )
INSERT INTO #TEMP([Id],[Name],[Total],[Status])
SELECT  1,  'Kitu',     565,    'SC'            UNION ALL
SELECT  2,  'Kavya',    576,    'FC'            UNION ALL
SELECT  3,  'rajni',    98,     '07/08/2011'    UNION ALL
SELECT  4,  'devi',     49,     '07/05/2010'    UNION ALL
SELECT  5,  'laya',     637,    'FC'            UNION ALL
SELECT  6,  'gita',     32,     '09/08/2011'    UNION ALL
SELECT  7,  'guru',     981,    'FCD'           UNION ALL
SELECT  8,  'dev',      63,     '07/05/2012'    UNION ALL
SELECT  9,  'magi',     45,     '09/10/2011'    UNION ALL
SELECT  10, 'malya',    562,    'SC'


SELECT  *
FROM    #TEMP
ORDER
BY      CASE
            -- when [Status] is date then converts date from MM/DD/YYYY format to YYYYMMDD FORMAT
            WHEN ISDATE( [Status] ) = 1 THEN CONVERT( VARCHAR(50), CONVERT( DATETIME, [Status] ), 112 )
            ELSE [Status]
        END

kalpanasv · Answer 3 · 2011-09-06T01:44:00

Solution 2

If the function return the table with RunningBalance column of any datatype . its working fine with above query.

Posted 6-Sep-11 1:44am

kalpanasv

Sorting in SQL Functions

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0