Order by rank

Hello
I have this query which works fine, but I would like to order by rank, so i
get most "correct" results first.....
SELECT * FROM Questions
WHERE FREETEXT(Question, @.Keywords)
OR FREETEXT(Answer, @.Keywords)
OR FREETEXT(Headline, @.Keywords)
TIA
/Lasse
here's how I would tackle this:
declare declare @.keywords varchar(100)
set @.keywords='microsoft'
select rank= case when search1.rank>=search2.rank and
search1.rank>=search3.rank then search1.rank
when search2.rank>=search3.rank and search2.rank>=search1.rank then
search2.rank
when search3.rank>=search1.rank and search3.rank>=search1.rank then
search3.rank end
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||Lasse,
I don't want to assume anything here, but in order by rank, you would need
to use FREETEXTTABLE as FREETEXT does not provide that functionality. Again,
not wanting to assume anything, without having you provide more information,
but do you want a one column to be "ranked" higher than the over columns in
your tables?
More information on what you are trying to achieve would be helpful in order
to provide a more specific solution for you.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||John,
no column should be ranked higher than other.
works fine with "rank" when one column, but not sure how to do it when there
are 3 columns.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
> Lasse,
> I don't want to assume anything here, but in order by rank, you would need
> to use FREETEXTTABLE as FREETEXT does not provide that functionality.
Again,
> not wanting to assume anything, without having you provide more
information,
> but do you want a one column to be "ranked" higher than the over columns
in
> your tables?
> More information on what you are trying to achieve would be helpful in
order[vbcol=seagreen]
> to provide a more specific solution for you.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
so
> i
>
|||If that's the case you should add them respective ranks, as illustrated
below.
declare @.keywords varchar(100)
set @.keywords='microsoft'
select question, answer, headline, rank=
search1.rank+search2.rank+search3.rank
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||Lasse,
Ok... and that's why I assume nothing and try to gather information first
before answering questions... <G>
You can use FREETEXTTABLE in the following multiple table & columns SQL FTS
query:
SELECT distinct e.OrderNo, e.Label
from ItemStock AS e, ItemTitles t, ItemHardware h,
containstable(ItemStock, Label, 'Billy') as A,
containstable(ItemTitles, Title, 'Stranger') as B,
containstable(ItemHardware, s_page, 'row') as C
where
A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
therefore needs distinct e.OrderNo.
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
Substitute ItemStock for your table Question, ItemTitles for your table
Answer and ItemHardware for your table Headline. Note, all the above tables
have Primary key - Foreign Key relationships as should your tables in order
for the joins to work correctly. You can also alter the above to have OR
conditions between the containstable (or freetexttable) clauses, but you
will need to use the distinct parameter to eliminate the duplicate rows. Let
me know if you need the DDL (create table, etc.) for the above tables as I
can email them to you if you want.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||What the heck?
In the original post the poster was using freetext where Question, Answer
and Headline would refer to columns in the table Questions.
Secondly the poster wanted to order by rank. I don't see anything in here
where you order by rank.
Are you possibly assuming something?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Lasse,
> Ok... and that's why I assume nothing and try to gather information first
> before answering questions... <G>
> You can use FREETEXTTABLE in the following multiple table & columns SQL
FTS
> query:
> SELECT distinct e.OrderNo, e.Label
> from ItemStock AS e, ItemTitles t, ItemHardware h,
> containstable(ItemStock, Label, 'Billy') as A,
> containstable(ItemTitles, Title, 'Stranger') as B,
> containstable(ItemHardware, s_page, 'row') as C
> where
> A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
> therefore needs distinct e.OrderNo.
> B.[KEY] = t.OrderNo and
> C.[KEY] = h.OrderNo
>
> Substitute ItemStock for your table Question, ItemTitles for your table
> Answer and ItemHardware for your table Headline. Note, all the above
tables
> have Primary key - Foreign Key relationships as should your tables in
order
> for the joins to work correctly. You can also alter the above to have OR
> conditions between the containstable (or freetexttable) clauses, but you
> will need to use the distinct parameter to eliminate the duplicate rows.
Let[vbcol=seagreen]
> me know if you need the DDL (create table, etc.) for the above tables as I
> can email them to you if you want.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> there
> need
columns[vbcol=seagreen]
rank,
>
|||In Lasse's reply to me, he stated that "no column should be ranked higher
than other.", so all that needs to be changed / added to my query example is
substituting freetexttable for containstable and adding an ORDER BY clause,
for example:
SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
from ItemTitles AS t,
freetexttable(ItemTitles, Title, 'title') as A,
freetexttable(ItemTitles, Artist, 'microsoft') as B,
freetexttable(ItemTitles, Location, 'else') as C
where
A.[KEY] = t.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = t.OrderNo
ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
So, Hilary, I wasn't assuming anything, I did mis-read the initial question
(as you have as well from time-to-time) to indicate multiple tables, vs. a
single table with multiple columns and I've altered the above query to
correct this mistake. I was replying to Lasse's most recent post and I'll
wait for Lasse to reply with his feedback to this post as well. Please, feel
free to email me directly, if you have any questions &/or concerns.
A question for Lasse - Are you trying to get results from just one column or
across all three columns?
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What the heck?
> In the original post the poster was using freetext where Question, Answer
> and Headline would refer to columns in the table Questions.
> Secondly the poster wanted to order by rank. I don't see anything in here
> where you order by rank.
> Are you possibly assuming something?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> FTS
> tables
> order
> Let
I[vbcol=seagreen]
would[vbcol=seagreen]
functionality.[vbcol=seagreen]
> columns
in
> rank,
>
|||now, I'm even more confused. Lasse said ""no column should be ranked higher
than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank desc.
This I really don't understand.
Surely you mean a.rank desc, b.rank desc, c.rank desc?
What am I missing?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
> In Lasse's reply to me, he stated that "no column should be ranked higher
> than other.", so all that needs to be changed / added to my query example
is
> substituting freetexttable for containstable and adding an ORDER BY
clause,
> for example:
> SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
> from ItemTitles AS t,
> freetexttable(ItemTitles, Title, 'title') as A,
> freetexttable(ItemTitles, Artist, 'microsoft') as B,
> freetexttable(ItemTitles, Location, 'else') as C
> where
> A.[KEY] = t.OrderNo and
> B.[KEY] = t.OrderNo and
> C.[KEY] = t.OrderNo
> ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
>
> So, Hilary, I wasn't assuming anything, I did mis-read the initial
question
> (as you have as well from time-to-time) to indicate multiple tables, vs. a
> single table with multiple columns and I've altered the above query to
> correct this mistake. I was replying to Lasse's most recent post and I'll
> wait for Lasse to reply with his feedback to this post as well. Please,
feel
> free to email me directly, if you have any questions &/or concerns.
> A question for Lasse - Are you trying to get results from just one column
or[vbcol=seagreen]
> across all three columns?
> Best Regards,
> John
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...
Answer[vbcol=seagreen]
here[vbcol=seagreen]
> first
SQL[vbcol=seagreen]
table[vbcol=seagreen]
OR[vbcol=seagreen]
you[vbcol=seagreen]
rows.[vbcol=seagreen]
as[vbcol=seagreen]
> I
when[vbcol=seagreen]
> would
> functionality.
helpful
> in
>
|||Well, I just threw that in for you while waiting for Lasse's reply!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:et9$kFtlEHA.704@.TK2MSFTNGP09.phx.gbl...
> now, I'm even more confused. Lasse said ""no column should be ranked
higher
> than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
> So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
> than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank
desc.[vbcol=seagreen]
> This I really don't understand.
> Surely you mean a.rank desc, b.rank desc, c.rank desc?
> What am I missing?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
higher[vbcol=seagreen]
example[vbcol=seagreen]
> is
> clause,
> question
a[vbcol=seagreen]
I'll[vbcol=seagreen]
> feel
column[vbcol=seagreen]
> or
> Answer
> here
> SQL
> table
in[vbcol=seagreen]
have[vbcol=seagreen]
> OR
> you
> rows.
tables[vbcol=seagreen]
> as
> when
> helpful
by
>