March 20, 2012 at 12:53 pm
I have a query result which i want to sort in an unusual way or at least a way I have yet come accross!
its sort of like a query result of clients with multiple orders
with five columns
ClientID, OrderID, OrderStatus, OrderDate, closedDate (to put it simply)
I want to return the results so that the most recent order date is at the top
followed by the other orders with same clientID in descending date order
next should be the 2nd most recent order date followed by
the other orders with same clientID in descending date order and so on
March 20, 2012 at 1:02 pm
easily doable...
Can you have a gander the following article and provide us some extra information on what you are trying to acheive?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2012 at 1:03 pm
You'll need to use a ranking function. Perhaps posting your script will make it a bit easier for these kind folk to help you out.
DDL would be even better!
March 20, 2012 at 1:55 pm
okay this is my result set and my query is below,
Oh B*** i forgot to put in ClientID in the result set
Select'35372PF','Birks T','HPOT','1.Active','19/03/2012','01/01/1900' Union All
Select'35625PF','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900' Union All
Select'35625FH','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900' Union All
Select'33371PF','Worthington M','HDIS','1.Active','14/03/2012','01/01/1900' Union All
Select'35540FH','Hill Y','HHOM','1.Active','12/03/2012','01/01/1900' Union All
Select'35560WB','Cui W','WHBT','4.Closed Not Reported','12/03/2012','12/03/2012' Union All
Select'35458FH','Khan S','HMOR','1.Active','08/03/2012','01/01/1900' Union All
Select'35372FH','Birks T','HPOT','1.Active','06/03/2012','01/01/1900' Union All
Select'35300TD','Cui W','DPDE','1.Active','02/03/2012','01/01/1900' Union All
Select'35316TH','Jaszczyk J','HHOM','1.Active','02/03/2012','01/01/1900' Union All
Select'35088WB','Jaszczyk J','WTAX','1.Active','24/02/2012','01/01/1900' Union All
Select'35073WB','Caleb J','WESA','1.Active','24/02/2012','01/01/1900' Union All
Select'35022PF','Jordon C','HREP','1.Active','24/02/2012','01/01/1900' Union All
Select'35022FH','Jordon C','HREP','1.Active','23/02/2012','01/01/1900' Union All
Select'33371FH','Worthington M','HDIS','4.Closed Not Reported','03/01/2012','15/03/2012' Union All
Select'31886WB','Julin-Stringer K','WSFP','5.Closed Reported','01/11/2011','01/02/2012' Union All
Select'31373FH','Hill Y','HMOR','3.Supvr Complete','13/10/2011','01/01/1900' Union All
Select'19134WB','Julin-Stringer K','WOTH','5.Closed Reported','05/07/2011','01/10/2011' Union All
Select'28935TD','Julin-Stringer K','DPDE','5.Closed Reported','06/06/2011','01/10/2011' Union All
Select'24624TH','Storey C','HHOM','5.Closed Reported','04/05/2011','01/10/2011' Union All
Select'22621FH','Khan S','HMOR','5.Closed Reported','28/04/2011','01/12/2011' Union All
Select'28529TD','Storey C','DMDE','5.Closed Reported','27/04/2011','01/10/2011' Union All
Select'24234TH','Julin-Stringer K','HLAN','5.Closed Reported','28/03/2011','01/12/2011'
And here is my Query
SELECT c.MainCaseRef
, l.Surname + ' ' + LEFT(l.Forename, 1) AS Client
, mt.code
, sts.status
, c.Dateopened
, c.dateclosed
FROM Cases AS c INNER JOIN
MatterTypes mt ON c.MatterType1ID = mt.ID INNER JOIN
Clients AS l ON c.ClientID = l.ID INNER JOIN
CaseWorkers AS W ON c.CaseWorkerID = W.ID INNER JOIN
View_CaseStatus Sts ON c.ID = Sts.ID Inner Join
(Select Ca.ClientID , Count(*) as Counted
From Cases Ca INNER JOIN
View_CaseStatus AS Sts ON Ca.ID = Sts.ID
WHERE NOT (Sts.status Like '6%' or status like 'z%' OR (Ca.FundingTypeID = 3 and Status Like '4%'))
Group By ClientID
Having Count(*) > 1) as MMND on l.id = MMND.clientID
WHERE NOT (Sts.status Like '6%' or status like 'z%' OR (C.FundingTypeID = 3 and Status Like '4%'))
order by Dateopened Desc
March 20, 2012 at 1:59 pm
now out of the result set, how do u want to order it?
can u show your desired output?
March 20, 2012 at 2:11 pm
oh sorry i didnt realise you waqnted me to lay it out in the desired output
I had origionally put in the order it was which was Date desc
Here is how i would like it be
Select'35372PF','Birks T','HPOT','1.Active','19/03/2012','01/01/1900' Union All
Select'35372FH','Birks T','HPOT','1.Active','06/03/2012','01/01/1900' Union All
Select'35625PF','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900' Union All
Select'35625FH','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900' Union All
Select'33371PF','Worthington M','HDIS','1.Active','14/03/2012','01/01/1900' Union All
Select'33371FH','Worthington M','HDIS','4.Closed Not Reported','03/01/2012','15/03/2012' Union All
Select'35540FH','Hill Y','HHOM','1.Active','12/03/2012','01/01/1900' Union All
Select'31373FH','Hill Y','HMOR','3.Supvr Complete','13/10/2011','01/01/1900' Union All
Select'35560WB','Cui W','WHBT','4.Closed Not Reported','12/03/2012','12/03/2012' Union All
Select'35300TD','Cui W','DPDE','1.Active','02/03/2012','01/01/1900' Union All
Select'35458FH','Khan S','HMOR','1.Active','08/03/2012','01/01/1900' Union All
Select'22621FH','Khan S','HMOR','5.Closed Reported','28/04/2011','01/12/2011' Union All
Select'35316TH','Jaszczyk J','HHOM','1.Active','02/03/2012','01/01/1900' Union All
Select'35088WB','Jaszczyk J','WTAX','1.Active','24/02/2012','01/01/1900' Union All
Select'35073WB','Caleb J','WESA','1.Active','24/02/2012','01/01/1900' Union All
Select'35022PF','Jordon C','HREP','1.Active','24/02/2012','01/01/1900' Union All
Select'35022FH','Jordon C','HREP','1.Active','23/02/2012','01/01/1900' Union All
Select'31886WB','Julin-Stringer K','WSFP','5.Closed Reported','01/11/2011','01/02/2012' Union All
Select'19134WB','Julin-Stringer K','WOTH','5.Closed Reported','05/07/2011','01/10/2011' Union All
Select'28935TD','Julin-Stringer K','DPDE','5.Closed Reported','06/06/2011','01/10/2011' Union All
Select'24234TH','Julin-Stringer K','HLAN','5.Closed Reported','28/03/2011','01/12/2011' Union All
Select'24624TH','Storey C','HHOM','5.Closed Reported','04/05/2011','01/10/2011' Union All
Select'28529TD','Storey C','DMDE','5.Closed Reported','27/04/2011','01/10/2011'
March 20, 2012 at 2:23 pm
Give this a go
SELECT
ROW_NUMBER() over(partition by l.surname + ' ' + left(l.forename,1) order by c.dateopened) num
, c.MainCaseRef
, l.Surname + ' ' + LEFT(l.Forename, 1) AS Client
, mt.code
, sts.status
, c.Dateopened
, c.dateclosed
FROM Cases AS c INNER JOIN
MatterTypes mt ON c.MatterType1ID = mt.ID INNER JOIN
Clients AS l ON c.ClientID = l.ID INNER JOIN
CaseWorkers AS W ON c.CaseWorkerID = W.ID INNER JOIN
View_CaseStatus Sts ON c.ID = Sts.ID Inner Join
(Select Ca.ClientID , Count(*) as Counted
From Cases Ca INNER JOIN
View_CaseStatus AS Sts ON Ca.ID = Sts.ID
WHERE NOT (Sts.status Like '6%' or status like 'z%' OR (Ca.FundingTypeID = 3 and Status Like '4%'))
Group By ClientID
Having Count(*) > 1) as MMND on l.id = MMND.clientID
WHERE NOT (Sts.status Like '6%' or status like 'z%' OR (C.FundingTypeID = 3 and Status Like '4%'))
order by client Desc
Should give you results similar to
135372FHBirks THPOT1.Active06/03/201201/01/1900
235372PFBirks THPOT1.Active19/03/201201/01/1900
135073WBCaleb JWESA1.Active24/02/201201/01/1900
135300TDCui WDPDE1.Active02/03/201201/01/1900
135625PFGregson MHRNT1.Active14/03/201201/01/1900
235625FHGregson MHRNT1.Active14/03/201201/01/1900
135540FHHill YHHOM1.Active12/03/201201/01/1900
231373FHHill YHMOR3.Supvr Complete13/10/201101/01/1900
135316THJaszczyk JHHOM1.Active02/03/201201/01/1900
235088WBJaszczyk JWTAX1.Active24/02/201201/01/1900
135022FHJordon CHREP1.Active23/02/201201/01/1900
235022PFJordon CHREP1.Active24/02/201201/01/1900
131886WBJulin-Stringer KWSFP5.Closed Reported01/11/201101/02/2012
219134WBJulin-Stringer KWOTH5.Closed Reported05/07/201101/10/2011
328935TDJulin-Stringer KDPDE5.Closed Reported06/06/201101/10/2011
424234THJulin-Stringer KHLAN5.Closed Reported28/03/201101/12/2011
March 21, 2012 at 4:37 am
Thanks very much
its not quite there yet
i have modified a little using the clients.ID
ROW_NUMBER() over(partition by l.id order by c.dateopened Desc) num
and also
order by l.id DESC
This rownumber function is sequentially numbering each record for each client.id ordered by dateopened DESC i sort of get that , pretty cool:-)
but the final order by (client.id Desc) means that the most recently created client appears at the top of the list and not the most recently created case
Isnt this the same as saying order by Client.ID Desc , Dateopened Desc
in fact the ranking function is not affecting the sort at all ??
the sort is being done by Client in your example
the most recent dateopened should be the first record, immediatley followed by
all other records with the same client ID
after that the next most recent dateopened ,
followed by all other records with the same client ID
So HOW do i organise the ranking ?? I cant see how ranking is helping :unsure:
March 21, 2012 at 8:57 am
Alright, still flying blind without your table scripts which would make this tons easier.
I added a join to a derived table which sorts the clients by their most recent dateOpened then sorted by this number.
SELECT
c.MainCaseRef
, l.Surname + ' ' + LEFT(l.Forename, 1) AS Client
, mt.code
, sts.status
, c.Dateopened
, c.dateclosed
FROM Cases AS c
INNER JOIN MatterTypes mt
ON c.MatterType1ID = mt.ID
INNER JOIN Clients AS l
ON c.ClientID = l.ID
INNER JOIN CaseWorkers AS W
ON c.CaseWorkerID = W.ID
INNER JOIN View_CaseStatus Sts
ON c.ID = Sts.ID
Inner Join (Select
Ca.ClientID ,
Count(*) as Counted
From Cases Ca
INNER JOIN View_CaseStatus AS Sts
ON Ca.ID = Sts.ID
WHERE NOT (Sts.status Like '6%'
or status like 'z%'
OR (Ca.FundingTypeID = 3
and Status Like '4%'))
Group By ClientID
Having Count(*) > 1) as MMND
on l.id = MMND.clientID
LEFT JOIN (SELECT row_number() over(order by max(c1.dateopened) desc) sort,
l1.id client,
max(c1.dateopened) dates
FROM cases c1
INNER JOIN clients l1
ON c1.clientID = l1.ID
GROUP BY client) m
ON l.ID = l1.ID
WHERE NOT (Sts.status Like '6%' or status like 'z%' OR (C.FundingTypeID = 3 and Status Like '4%'))
ORDER BY l1.sort desc
March 21, 2012 at 9:31 am
i'm trying to imagine whats hapeneing there
but your code didnt work when i pasted it pasted error below
But basically Your doing another query and joining to All rows of the initial query
okay im gonna have a go at trying to get what you given me to work.
if i get lost ill re post
not sure how to actually provide all the info you want without
typing it manually
or maybe you will notice something obvious if you see the error msg
Msg 207, Level 16, State 1, Line 36
Invalid column name 'client'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'ID'.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "l1.sort" could not be bound.
March 21, 2012 at 9:37 am
Yea, I can't test the script because I don't have your tables. You can script the tables by going to the object explorer and right click > script table as > create to > [choose your destination]
Make the following change to the group by clause in the derived table and also the change to the main order by clause at the end.
...
GROUP BY l1.ID) m
...
ORDER BY m.sort DESC
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply