Different Orders in Order By in the same SELECT
-
Got a simple yet wierd problem:
I have 2 tables:
CREATE TABLE [Table1] (
[OrderID_] [int] IDENTITY (1000, 1) NOT NULL ,
[TypeCode_] [varchar] (12) NOT NULL ,
[CreationDate_] [datetime] NOT NULL ,
[SubmittedDate_] [datetime] NULL ,
[StatusCode_] [varchar] (12) NOT NULL
 
ON [PRIMARY]CREATE TABLE [Table2] (
[Code_] [varchar] (12) NOT NULL ,
[Description_] [varchar] (40) NOT NULL ,
[SearchOrder_] [smallint] NULL
 
ON [PRIMARY]Table1.StatusCode_ = Table2.Code_ which can be: "Incomplete", "Submitted", "Cancelled", "Pending", etc
Table2.SeachOrder has 1 for "Incomplete", 2 for "Submitted" and "Cancelled" and 3 for rest.
The requirements is display (select) Orders (OrderID) or everything from Table1 with "Incomplete" orders first first. "Incomplete" orders are then sorted by CreationDate in descending order (last displayed first). "Submitted" and "Cancelled" Orders have equal priority and are shown next sorted by Submitted date in ascending order (most recent first)
I wrote:
select t1.* from Table1 t1
left outer join Table2 t2 on t1.StatusCode_ = t2.Code_
order by t2.SearchOrder_ asc, t1.creationDate_ desc, t1.submittedDate_ asc
This does not solve our purpose, since the last order by column t1.submittedDate_ does not hold much importance, it mainly depends upon the order of the previous 2 columns.
Also tried:
select t1.* from table1 t1
left outer join table2 t2 on t1.StatusCode_ = t2.Code_
order by
t2.SearchOrder_ asc,
CASE t1.StatusCode_
WHEN 'I' THEN t1.creationDate_ --desc
WHEN 'S' THEN t1.submittedDate_ --asc
END
This is not acceptable either since Desc keyword fails the statement with syntax error and the result set is ascending.
Is it possible to acheive this by a single SELECT? Or do I have to build separate temp tables and then UNION the results?
Any suggestions will be appreciated
Gary
-
Seems like you do not even need to go after T2 since you are not selecting anything out of it and the STATUS-CODE column in T1 itself can be used for sorting the results (unless I understood the requirements incorrectly):
CREATE TABLE [Table1] (
[OrderID] [int] IDENTITY (1000, 1) NOT NULL ,
[TypeCode] [varchar] (12) NOT NULL ,
[CreationDate] [datetime] NOT NULL ,
[SubmittedDate] [datetime] NULL ,
[StatusCode] [varchar] (12) NOT NULL
)
INSERT INTO TABLE1 (TYPECODE, CREATIONDATE, SUBMITTEDDATE, STATUSCODE)
VALUES ('X', GETDATE(), DATEADD (hh, 10, GETDATE()), 'I')
INSERT INTO TABLE1 (TYPECODE, CREATIONDATE, SUBMITTEDDATE, STATUSCODE)
VALUES ('Y', DATEADD (hh, 10, GETDATE()), DATEADD (hh, 100, GETDATE()), 'I')
INSERT INTO TABLE1 (TYPECODE, CREATIONDATE, SUBMITTEDDATE, STATUSCODE)
VALUES ('Z', DATEADD (hh, 100, GETDATE()), DATEADD (hh, 1000, GETDATE()), 'I')
INSERT INTO TABLE1 (TYPECODE, CREATIONDATE, SUBMITTEDDATE, STATUSCODE)
VALUES ('X', GETDATE(), DATEADD (hh, 200, GETDATE()), 'S')
INSERT INTO TABLE1 (TYPECODE, CREATIONDATE, SUBMITTEDDATE, STATUSCODE)
VALUES ('Y', DATEADD (hh, 200, GETDATE()), DATEADD (hh, 2000, GETDATE()), 'S')
SELECT STATUSCODE, * FROM TABLE1
--OUTPUT
statuscode OrderID TypeCode CreationDate SubmittedDate StatusCode
------------ ----------- ------------ ------------------------------------------------------ ------------------------------------------------------ ------------
I 1000 X 2005-10-22 10:10:22.690 2005-10-22 20:10:22.690 I
I 1001 Y 2005-10-22 20:10:22.723 2005-10-26 14:10:22.723 I
I 1002 Z 2005-10-26 14:10:22.723 2005-12-03 02:10:22.723 I
S 1003 X 2005-10-22 10:10:22.723 2005-10-30 18:10:22.723 S
S 1004 Y 2005-10-30 18:10:22.723 2006-01-13 18:10:22.723 S
--Replace * with the columns that you really need
SELECT *
FROM TABLE1
ORDER BY
(CASE STATUSCODE
WHEN 'I' THEN 2
WHEN 'S' THEN 1
END) DESC,
(CASE STATUSCODE
WHEN 'I' THEN CREATIONDATE
ELSE NULL
END) DESC,
(CASE STATUSCODE
WHEN 'S' THEN SUBMITTEDDATE
ELSE NULL
END) ASC
--OUTPUT
OrderID TypeCode CreationDate SubmittedDate StatusCode
----------- ------------ ------------------------------------------------------ ------------------------------------------------------ ------------
1002 Z 2005-10-26 14:10:22.723 2005-12-03 02:10:22.723 I
1001 Y 2005-10-22 20:10:22.723 2005-10-26 14:10:22.723 I
1000 X 2005-10-22 10:10:22.690 2005-10-22 20:10:22.690 I
1003 X 2005-10-22 10:10:22.723 2005-10-30 18:10:22.723 S
1004 Y 2005-10-30 18:10:22.723 2006-01-13 18:10:22.723 S
The trick is to sort the status-code with I first by using a higher number value, then within that, sort by creationdate desc, and then for the S status code records, sort by submitteddate ASC.
Hth
-
Fantastic - RSharma U rock
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply