order by importance

hi i have an association rule mining model and i want to order the output by the importance here is the select statment:

SELECT

[RelatedOrder].[Order Line]

From

[RelatedOrder]

NATURAL PREDICTION JOIN

(SELECT (SELECT 888 AS [Product ID]) AS [Order Line]) AS t

thanx

You can do the following to get the top N predictions ordered by importance:

SELECT FLATTENED TopCount(Predict([RelatedOrder].[Order Line], INCLUDE_STATISTICS), $AdjustedProbability, N)

From

[RelatedOrder]

NATURAL PREDICTION JOIN

(SELECT (SELECT 888 AS [Product ID]) AS [Order Line]) AS t

If you really want to get all the predictions, the following should work:

SELECT * FROM

(SELECT FLATTENED Predict([RelatedOrder].[Order Line], INCLUDE_STATISTICS) AS PredictedProducts

From

[RelatedOrder]

NATURAL PREDICTION JOIN

(SELECT (SELECT 888 AS [Product ID]) AS [Order Line]) AS t) AS P

ORDER BY [PredictedProducts.$AdjustedProbability]

|||

Here is a different solution for top N predictions sorted by importance:

SELECT FLATTENED Predict([Order Line], N, INCLUDE_STATISTICS, $ADJUSTEDPROBABILITY) FROM [RelatedOrder] NATURAL PREDICTION JOIN ...

This solution is less general (it applies only to nested table predictions, while TopCount applies to any nested table), but typically has better performance (growing to much better if the number of keys in the nested table is large)

Predict([Order Line]) -- returns all predictions from the table

Predict([Order Line], INCLUDE_STATISTICS) -- returns all predictions from the table plus statistics

Predict([Order Line], N, INCLUDE_STATISTICS) -- returns top N predictions sorted by probability (default) from plus statistics

Predict([Order Line], N, INCLUDE_STATISTICS, $ADJUSTEDPROBABILITY) does same as above, but sorts by Adjusted Probability (what you actually need)

Order by & case

I am trying to use a case statment to indicate sort order. Everything worked fine untill I added "desc".

create table #tmp(f1 varchar(25), dtObs datetime, dtCnt datetime)
insert into #tmp values('ABC','01-Jan-2003','02-Jan-2003')
insert into #tmp values('BCD','01-Jan-2003','03-Jan-2003')
insert into #tmp values('CDE','01-Jan-2003','04-Jan-2003')
insert into #tmp values('DEF','02-Jan-2003','03-Jan-2003')
insert into #tmp values('EFG','02-Jan-2003','04-Jan-2003')
insert into #tmp values('FGH','02-Jan-2003','05-Jan-2003')

declare @.Order tinyint
set @.Order = 1
select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
when 3 then 'dtObs desc'
when 4 then 'dtCnt desc'
else null end

any suggestion on how to do this?select *
from #tmp
order by case @.Order when 1 then 'dtObs'
when 2 then 'dtCnt'
else null end
,case @.Order when 3 then 'dtObs'
when 4 then 'dtCnt'
else null end DESC|||Thanks!|||Pleasure to help you.|||I am using a Case statement within my Order By clause, but I have columns that I want to use that were created in my Select clause as expressions. I cannot seem to use those columns in my Case statement. Is there a way to do this?? Thank you very much for your help!!!sql