October 19, 2010 at 11:40 am
I have the following query:
CREATE TABLE #Date
(
rowIDINT IDENTITY(1,1),
rxDate VARCHAR(50)
)
INSERT INTO #Date
SELECT DISTINCT rxDate FROM dbo.TB_disRxPeriode
INNER JOIN dbo.TB_disFichePatient ON dbo.TB_disRxPeriode.ficheID = dbo.TB_disFichePatient.ficheID
WHERE dbo.TB_disFichePatient.ficheNoDossier = 'GAGD07077502'
ORDER BY rxDate DESC;
WITH CTE AS (
SELECT
per.perID,
per.rxDate,
rowID,
cnt.cntNom,
cnt.cntOrdre,
CASE WHEN cntNom = 'MATIN' THEN med.medFreqAM
WHEN cntNom = 'MIDI' THEN med.medFreqMD
WHEN cntNom = 'SOUPER' THEN med.medFreqSP
WHEN cntNom = 'COUCHER' THEN med.medFreqHS
END Quantite,
medi.medNom,
medi.medTemplateFile
FROM dbo.TB_disFichePatient fic
INNER JOIN dbo.TB_disRxPeriode per ON fic.ficheID = per.ficheID
INNER JOIN dbo.TB_disRxList list ON per.perID = list.perID
INNER JOIN dbo.TB_disListMed med ON list.idxList = med.idxList AND med.ficheID = fic.ficheID
INNER JOIN dbo.TB_disConstante cnt ON per.nomID = cnt.cntID
INNER JOIN dbo.TB_disMedicament medi ON med.medID = medi.medID
INNER JOIN #Date ON #Date.rxDate = per.rxDate
WHERE fic.ficheNoDossier = 'GAGD07077502'
)
SELECT DISTINCT
a.perID,
a.rxDate,
a.rowID,
a.cntNom,
a.cntOrdre,
STUFF(
(SELECT '; ' + CAST(Quantite AS VARCHAR) + ',' + medNom + ',' + medTemplateFile FROM CTE WHERE perID = a.perID FOR XML PATH('')),
1, 2, '') as Posologie
FROM CTE a
ORDER BY a.rxDate DESC, CASE WHEN (a.rowID % 2) = 1 THEN a.cntOrdre END, CASE WHEN (a.rowID % 2 = 0) THEN a.cntOrdre END DESC;
If I remove the CASE WHEN in the Order By, it works fine, but when I put it back, I have the following error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I just can't find out how to make it work.
anyone can help?
thanks for your time and help
October 19, 2010 at 12:17 pm
You need to provide a column name/alias for the ORDER BY clause to work - the CASE statement won't work there. From BOL
[ ORDER BY
{
order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
} [ ,...n ]
]
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list
Try something like this:
DECLARE @T TABLE(Val1 int, Val2 int)
INSERT @T(Val1,Val2)
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,5
SELECT * FROM @T
-- order by odd #'s and then by even #'s
--SELECT DISTINCT Val1,Val2 FROM @T ORDER BY CASE WHEN (Val2%2=0) THEN Val2 END DESC --> doesn't work
SELECT DISTINCT Val1,Val2,CASE WHEN Val2%2=0 THEN 0 ELSE 1 END OrderOdd
FROM @T ORDER BY OrderOdd DESC
October 19, 2010 at 12:37 pm
Could you describe briefly what you want your case in the order by to accomplish?
There are multiple things wrong here (as noted above, the order by needs to be a selected column, but you also built your case statements in a weird way that may not sort as you want even if you get the command executing).
So what do you want in terms of order?
October 19, 2010 at 1:04 pm
thanks for the replies.
I figured out that, by using a derived table, it works fine:
SELECT * FROM
(
SELECT DISTINCT
a.perID,
a.rxDate,
a.rowID,
a.cntNom,
a.cntOrdre,
STUFF(
(SELECT '; ' + CAST(Quantite AS VARCHAR) + ',' + medNom + ',' + medTemplateFile FROM CTE WHERE perID = a.perID FOR XML PATH('')),
1, 2, '') as Posologie
FROM CTE a) AS x
ORDER BY x.rxDate DESC, CASE WHEN (x.rowID % 2) = 1 THEN x.cntOrdre END, CASE WHEN (x.rowID % 2 = 0) THEN x.cntOrdre END DESC;
My goal was to invert the Order By depending of the rowID value. ASC for even and DESC for odd.
October 19, 2010 at 1:33 pm
Dominic Gagné (10/19/2010)
thanks for the replies.I figured out that, by using a derived table, it works fine:
SELECT * FROM
(
SELECT DISTINCT
a.perID,
a.rxDate,
a.rowID,
a.cntNom,
a.cntOrdre,
STUFF(
(SELECT '; ' + CAST(Quantite AS VARCHAR) + ',' + medNom + ',' + medTemplateFile FROM CTE WHERE perID = a.perID FOR XML PATH('')),
1, 2, '') as Posologie
FROM CTE a) AS x
ORDER BY x.rxDate DESC, CASE WHEN (x.rowID % 2) = 1 THEN x.cntOrdre END, CASE WHEN (x.rowID % 2 = 0) THEN x.cntOrdre END DESC;
My goal was to invert the Order By depending of the rowID value. ASC for even and DESC for odd.
It seems like a weird requirement.
So assuming all our dates were the same for a dataset, you would want to see all the even numbered rows first (in descending order of cntOrdre), and then all odd numbered rows (in ascending order of cntOrdre)? Is that right?
October 20, 2010 at 6:36 am
The recordset will return 7 distinct date. Each days has 4 time periods (morning/noon/evening/bedtime). First day has to be in time period order (cntOrdre). Second day in reverse. Third day in order, and so on. Can look weird, but those data are then sent to an automated system who will analyze physically a kind of device and recordset have to match the analyze pattern (motorised camera movement).
October 20, 2010 at 7:07 am
Just as the message says,
Msg 145, Level 15, State 1, Line 14
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Try this, which works
;WITH TestCTE AS (
SELECT TOP 10
Today = GETDATE(),
[Name],
rn = ROW_NUMBER() OVER(ORDER BY NEWID())
FROM dbo.syscolumns
)
SELECT DISTINCT
Today,
[Name],
rn,
CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END,
CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END
FROM TestCTE
ORDER BY Today, CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END, CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END DESC
and this, which doesn't
;WITH TestCTE AS (
SELECT TOP 10
Today = GETDATE(),
[Name],
rn = ROW_NUMBER() OVER(ORDER BY NEWID())
FROM dbo.syscolumns
)
SELECT DISTINCT
Today,
[Name],
rn--,
--CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END,
--CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END
FROM TestCTE
ORDER BY Today, CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END, CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END DESC
It's not the columns in the ORDER BY which matter - it's the expressions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 20, 2010 at 7:42 am
thanks for the reply.
Using a derived table works, why?
and what's the best practice? using your example (which works fine, just tested it in my SP) or my derived table? what is best?
thanks
October 20, 2010 at 7:48 am
When you wrapped your query in another SELECT (sometimes - usefully - known as an onion select), you removed the DISTINCT. Remember "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
Which is best? The quick and easy answer is to time them.
If I were you, I'd look first at why you are using DISTINCT and aim to remove it from your query if possible.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 20, 2010 at 12:19 pm
There is probably a work-around to get rid of the Distinct, but right now I have no other alternative, otherwise the query return duplicates. And I'm not a hard-core T-SQL programmer, so I think I'll have to leave it that way for now.
thanks for your help, it did helped me a lot!
October 21, 2010 at 5:31 am
Hi there,
I was also faced with the same situation a week ago.. GROUP BY did the trick.. I'm not sure if this would be performance-wise so try testing it to see which is more efficient..
I slightly modified Chris's code.. Here it is:
;WITH TestCTE AS (
SELECT TOP 10
Today = GETDATE(),
[Name],
rn = ROW_NUMBER() OVER(ORDER BY NEWID())
FROM dbo.syscolumns
)
SELECT Today,
[Name],
rn--,
--CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END,
--CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END
FROM TestCTE
GROUP BY Today,
[Name],
rn--,
ORDER BY Today, CASE WHEN (rn % 2) = 1 THEN [Name] ELSE NULL END, CASE WHEN (rn % 2) = 0 THEN [Name] ELSE NULL END DESC
Hope you find it useful.. 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply