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 - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
David Portas
SQL Server MVP
--|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:

> SouRa wrote:
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.sql

ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.
You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
David Portas
SQL Server MVP
|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:

> SouRa wrote:
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.

ORDER BY - clause

Hi,
I have the following output from a SELECTion query.
Query: SELECT RollNo FROM Student
Output:
10
20
30
40
I want this result in following oder
20
40
10
30
Is it possible?
please advise me.
Thanks,
Soura.You need to specify why do you want that particular order? Is it ordered by
some other column or property?
MC
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:FDC26A6A-D5CC-4A41-817E-141D818D83F7@.microsoft.com...
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.|||SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.
You can put the ordering in a table and use that in your query:
CREATE TABLE roll_order
(rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
INSERT INTO roll_order (rollno, sequenceno)
SELECT 20,1 UNION ALL
SELECT 40,2 UNION ALL
SELECT 10,3 UNION ALL
SELECT 30,4 ;
SELECT S.rollno
FROM student AS S
JOIN roll_order AS R
ON S.rollno = R.rollno
ORDER BY R.sequenceno ;
If rollno is unique in the student table then you don't need another
table - you can put the sequenceno in student instead.
If you prefer not to create an extra column then try this:
SELECT rollno
FROM student
ORDER BY
CASE rollno
WHEN 20 THEN 1
WHEN 40 THEN 2
WHEN 10 THEN 3
WHEN 30 THEN 4
END ;
Hope this helps.
--
David Portas
SQL Server MVP
--|||if you know the specific order you want it in you can do this in your order
statemetn
(CASE numbercolumn WHEN '20' THEN 0 WHEN '40' THEN 1 WHEN '10' THEN 2 else
4 END )
but you have to specifically give every result a ranking so it will order
correctly
Kelly
"David Portas" wrote:
> SouRa wrote:
> > Hi,
> >
> > I have the following output from a SELECTion query.
> >
> > Query: SELECT RollNo FROM Student
> > Output:
> >
> > 10
> > 20
> > 30
> > 40
> >
> > I want this result in following oder
> >
> > 20
> > 40
> > 10
> > 30
> >
> > Is it possible?
> > please advise me.
> >
> > Thanks,
> > Soura.
>
> You can put the ordering in a table and use that in your query:
> CREATE TABLE roll_order
> (rollno INTEGER NOT NULL PRIMARY KEY, sequenceno INTEGER NOT NULL) ;
> INSERT INTO roll_order (rollno, sequenceno)
> SELECT 20,1 UNION ALL
> SELECT 40,2 UNION ALL
> SELECT 10,3 UNION ALL
> SELECT 30,4 ;
> SELECT S.rollno
> FROM student AS S
> JOIN roll_order AS R
> ON S.rollno = R.rollno
> ORDER BY R.sequenceno ;
> If rollno is unique in the student table then you don't need another
> table - you can put the sequenceno in student instead.
> If you prefer not to create an extra column then try this:
> SELECT rollno
> FROM student
> ORDER BY
> CASE rollno
> WHEN 20 THEN 1
> WHEN 40 THEN 2
> WHEN 10 THEN 3
> WHEN 30 THEN 4
> END ;
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Yet another solution:
CREATE TABLE Student (RollNo int not null)
INSERT INTO Student VALUES (10)
INSERT INTO Student VALUES (20)
INSERT INTO Student VALUES (30)
INSERT INTO Student VALUES (40)
SELECT RollNo
FROM Student
ORDER BY RollNo%20, RollNo
-- DROP TABLE Student
HTH,
Gert-Jan
SouRa wrote:
> Hi,
> I have the following output from a SELECTion query.
> Query: SELECT RollNo FROM Student
> Output:
> 10
> 20
> 30
> 40
> I want this result in following oder
> 20
> 40
> 10
> 30
> Is it possible?
> please advise me.
> Thanks,
> Soura.