ORDER BY Query Question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » ORDER BY Query Question

ORDER BY Query Question

Rate Topic

Display Mode

Topic Options

Author

Message

kylemkr85

Posted Friday, February 08, 2013 3:55 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:18 AM
Points: 11, Visits: 24

I have the following query below, but I want to ORDER BY a certain value in the Assigned To field first before it orders by ticket age. So lets say I have a AssignedTo value of 'Group1'. I want Group1 to be displayed first, then all remaining values to be displayed after by ticket age and group if that makes any sense.

Current Query:

SELECT DISTINCT
wcv.contact_name
,wcv.contact_organization_name
,wcv.workitem_title
,wcv.workitem_number
,wcv.workitem_created_on
,wcv.assigned_to_worker_name [Assigned To]
,datediff(d, workitem_created_on, getdate()) [Ticket Age]
FROM
contact_view cv
JOIN
workitem_current_view wcv
ON
cv.contact_email = wcv.contact_email
WHERE
wcv.workitem_status_lookup_value = 'Open' and wcv.workitem_created_on > '2006-01-01 12:00:00.000'
ORDER BY
[Ticket Age] DESC, [Assigned To] ASC

Post #1417938

kylemkr85

Posted Friday, February 08, 2013 3:57 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:18 AM
Points: 11, Visits: 24

To clarify a little, I want the results to be displayed by ticket age, however, I want a certain AssignedTo group to display first before the rest. I don't want all the AssignedTo groups grouped together however.

Post #1417939

Lynn Pettis

Posted Friday, February 08, 2013 4:12 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 21,600, Visits: 27,420

Give this a shot:


SELECT DISTINCT
    wcv.contact_name
    ,wcv.contact_organization_name
    ,wcv.workitem_title
    ,wcv.workitem_number
    ,wcv.workitem_created_on
    ,wcv.assigned_to_worker_name [Assigned To]
    ,datediff(d, workitem_created_on, getdate()) [Ticket Age]
FROM 
    contact_view cv
    JOIN workitem_current_view wcv
        ON cv.contact_email = wcv.contact_email
WHERE 
    wcv.workitem_status_lookup_value = 'Open' and
    wcv.workitem_created_on > '2006-01-01 12:00:00.000'
ORDER BY
    CASE WHEN wcv.assigned_to_worker_name = 'Group 1' THEN 0 ELSE 1 END ASC,
    [Ticket Age] DESC
    --[Assigned To] ASC

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1417943

kylemkr85

Posted Friday, February 08, 2013 4:16 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:18 AM
Points: 11, Visits: 24

Thanks for the quick response, however, I am receiving the following error below:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Post #1417947

Lynn Pettis

Posted Friday, February 08, 2013 6:56 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 21,600, Visits: 27,420

kylemkr85 (2/8/2013)

Thanks for the quick response, however, I am receiving the following error below:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Oh well, you get what you get when you don't provide DDL, sample data, and expected results. No tested code.

For better answers to your questions, please read and follow the instructions in the first article you will find referenced in my signature block below.

Now, how about this:


WITH BaseData AS (
SELECT DISTINCT
    wcv.contact_name
    ,wcv.contact_organization_name
    ,wcv.workitem_title
    ,wcv.workitem_number
    ,wcv.workitem_created_on
    ,wcv.assigned_to_worker_name [Assigned To]
    ,datediff(d, workitem_created_on, getdate()) [Ticket Age]
FROM 
    contact_view cv
    JOIN workitem_current_view wcv
        ON cv.contact_email = wcv.contact_email
WHERE 
    wcv.workitem_status_lookup_value = 'Open' and
    wcv.workitem_created_on > '2006-01-01 12:00:00.000'
)
SELECT
    contact_name,
    contact_organization_name,
    workitem_title,
    workitem_number,
    workitem_created_on,
    [Assigned To],
    [Ticket Age]
FROM
    BaseData
ORDER BY
    CASE WHEN [Assigned To] = 'Group 1' THEN 0 ELSE 1 END ASC,
    [Ticket Age] DESC

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1417969

« Prev Topic | Next Topic »

Permissions