Combining FREETEXTTABLE Rank With Another Ranking
Jul 26, 2007
I am using FREETEXTTABLE to enable users to search forum posts. It works extremely well (and fast!) and I am very happy with it.
I also have a top ten list of 'Most viewed' forum posts. Each time a post is accessed, a 'viewcount' value is incremented accordingly. The ten forum posts with the highest viewcount appear in the top ten list (by order of viewcount, of course).
Now... when a user searches on a search term, I would like the top ten list to also be affected by this search.
For example, the user searches on 'foo'. In response they get a list of results containing 'foo'.
However, the top ten list also changes to reflect this. It now shows the ten forum posts with the highest viewcount which also contain the word 'foo'.
This is easy enough to do. The problem arises when we also add proximity searches.
For example, if I search on 'foo bar', FREETEXTTABLE will look for records which have both or either of these words. Records which contain both words will be ranked higher than records which contain either word. And of the records which contain both words, the closer together the two words are, the higher the ranking will be.
But how does this now work with the top ten list? For example, I may have two records:
- Record A has been viewed 10 times. It contains the words 'foo' and 'bar' and they are next to each other.
- Record B has been viewed 10,000 times. But it only contains the word 'foo'.
Now, according to 'viewcount', record B should be ranked higher. But according to FREETEXTTABLE, record A should be ranked higher.
So, I need to combine both these rankings in some way. Regardless of how the 'top ten' records are chosen, they will be ordered by viewcount. But I'm not sure how to get the right top ten records in the first place.
Any thoughts would be much appreciated.
View 1 Replies
ADVERTISEMENT
Jun 9, 2007
i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.can i do this in the same command? what is the syntax?my code :select table1.field1,a.rank,b.rank,c.rankfrom table1containstable(table1,field,'"word"') as aor containstable(table1,field,'formsof(inflectional(word)') as b)or freetexttable(table1,field,'word') as cwhere table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]the above syntax is wrong. i tried ',' instead of 'or' but the results were not right. thanks
View 1 Replies
View Related
Mar 22, 2004
I'm trying to build a search function using FREETEXTTABLE to provide rankings of the results.
I've got it working on one of the tables using a query that looks something like this:
SELECT DISTINCT title, recordid, rank
FROM mn_records INNER JOIN FREETEXTTABLE(mn_records,*, 'sony') AS ft
ON mn_records.recordid = ft.[key]
ORDER BY rank DESC
The problem is that I want to search two tables mn_records and mn_items. A record has multiple items linked to it and some of the fields in the items table are different for the same record. I have the functionality working using FREETEXT:
SELECT DISTINCT title, recordid
FROM mn_records, mn_items
WHERE mn_records.recordid = mn_items.parentrecord
AND (FREETEXT (mn_records.*, 'sony')
OR FREETEXT (mn_items.*, 'sony'))
How can I properly join these two tables and the FREETEXTTABLE rank so that the search results will only contain distinct record_ids yet still search the information contained in mn_items?
View 1 Replies
View Related
Mar 21, 2006
This is my proc...
ALTER PROCEDURE [dbo].[IT_GetKBFreeText]
@Search Varchar(8000)
AS
BEGIN
SELECT KEY_TBL.RANK, KB_TBL.KNOWLEDGE_BASE_UID
FROM dbo.INFOSYS_KNOWLEDGE_BASE AS KB_TBL
INNER JOIN
FREETEXTTABLE(dbo.INFOSYS_KNOWLEDGE_BASE, (KNOWLEDGE_BASE_SHORT_NAME, KNOWLEDGE_BASE_TEXT),
@Search) AS KEY_TBL
ON KB_TBL.KNOWLEDGE_BASE_UID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
Iam returning rank = 0 for each record. FTS Is running and the catalog has all table records in in it ( I can tell by the record count)
Help!!!???
View 1 Replies
View Related
May 13, 2006
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@SearchString nvarchar(500),
@CultureName nvarchar(50),
@HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@CultureName)
AND FL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@CultureName)
AND PL.CultureID=dbo.GetCultureID(@CultureName)
SELECT @HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @HowManyResults int
EXEC SearchProject 'ملÙ?ات','ar-SA',@HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'ملÙ?ات') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
View 5 Replies
View Related
Mar 29, 2001
I have defined full-text indexing on a table a couple of months ago and the catalog was fully populated, it was working fine until now. But suddenly has stopped working, I have been scheduling incremental population on a regular basis and also have tried to remove and redefined full-text indexing but can't get it to work.
Can anyone help please
View 1 Replies
View Related
Jul 19, 2007
Hi,
We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.
here is the codesnippet:
select * from freetexttable(<tablename>, *, 'business')
Any help is greatly appreciated.
Baldwin
bbudiongan@misicompany.com
View 1 Replies
View Related
Dec 8, 2003
Hi everyone -
I have the following code on a database that works perfectly (i.e. searching for 'print' will return results that have 'printer' in the field):
***************************************
INSERT INTO searchResults (articleId, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, rank, hasAccess)
SELECT id, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, k.rank, hasAccess
FROM articles As a
INNER JOIN FREETEXTTABLE(articles, articlesummary, 'print') AS K
ON a.id = K.[KEY]
***************************************
I am working on a new DB trying to accomplish the same thing, so I have the following code:
***************************************
SELECT id, firstName, lastName, k.rank
FROM Pagers_Users As a
INNER JOIN FREETEXTTABLE(Pagers_Users, lastName, 'smith') AS K
ON a.id = K.[KEY]
ORDER BY k.rank DESC
***************************************
However, this second block does not completely work (as I see it). When I enter the word 'smith' to serch for, it only brings back exact matches, not close calls, like 'smithe'.
I have also tried searching for other words (or partial words) and it only returns exact matches.
Is there something in setting up the Text Index that I forgot to turn on for "close" matches?
-Thanks in advance
View 2 Replies
View Related
Jun 7, 2006
Hello,
I am building a simple full text search engine for my site and I was wondering how would I retrieve rows 11-20 of the search result. This is required because I want to show my results only 10 at a time, like google does for instance. My query is as follows -
select top 10 ft_tbl.url,
ft_tbl.title, ft_tbl.body, ft_tbl.date,
(key_tbl.rank)
from mytable as ft_tbl inner join
freetexttable(mytable, (url, title, body),
'".$searchstring."', 10) as key_tbl
on ft_tbl.id = key_tbl.[key]
order by (key_tbl.rank) DESC
In MySQL I would use LIMIT but I believe that doesnt exist in MS SQL
Thanks
View 2 Replies
View Related
Apr 8, 2008
Can somebody help me in identifying the difference between FREETEXTABLE and CONTAINSTABLE statements?
I have recently started experimenting with the free text search and I feel that FREETEXTTABLE can look for the search string into multiple columns by breaking it into tokens.
For example
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode
,key_tbl.rank
from temp as ft_tbl
INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl
ON FT_tbl.ID = key_tbl.[key]
Now in my case €œ80€? is in PAON column and €œRidge Avenue€? is in STREET column. Among the resulting rows it also displays the required row. If I try to achieve this with CONTAINSTABLE using
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode
,key_tbl.rank
from temp as ft_tbl
INNER JOIN containsTABLE(temp, (saon, paon, street, postcode), '80 ridge avenue') as key_tbl
ON FT_tbl.ID = key_tbl.[key]
It gives me error
Syntax error near 'ridge' in the full-text search condition '80 ridge avenue'.
If I make search string as €˜€?80 ridge avenue€?€™ instead of €˜ridge avenue€™ then it doesn€™t give me error but also displays no results because 80 ridge avenue does not appear as whole in any single column. If I search by only specifying €œridge avenue€? in search string then I get a full list where street is ridge avenue.
My question is Does FREETEXTTABLE can search in all listed column by breaking the search string into tokens and CONTAINSTABLE only in one as whole?
View 3 Replies
View Related
Aug 23, 2007
Hi,
I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name]
Can I use FREETEXTTABLE instead? How do I go about doing it?
View 2 Replies
View Related
Sep 13, 2007
I have an issue trying to pass a search text parameter to FREETEXTTABLE via Dataset.
The following code works fine if you hardcode the search word/text as shown:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, 'cool') AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
Now, I want to do this:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, @Search) AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
The error I'm getting is @Search is not declared. How am I suppose to pass in a value?
I have searched almost everywhere and nobody seemed to ask this precise question. I'm sure this is a huge problem.
Can anyone help me please?
View 3 Replies
View Related
Aug 31, 2007
We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:
Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC
vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.
This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.
I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?
View 4 Replies
View Related
Sep 12, 2007
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
View 1 Replies
View Related
Aug 11, 2007
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
View 3 Replies
View Related
Oct 22, 2007
I was wondering what kind of SQL statement I would need to do the following:For example I have a table that has an ID (vID) column, product id (vPID) and a timestamp (vTime). What I essentially want to do is go through this table and find the top 10 instances of any vPID. I will illsutrate it below.- vID vPID vTime 1 25 101012323 2 25 101012323 3 09 101012323 4 25 101012323 5 25 101012323 6 11 101012323 7 25 101012323 8 10 101012323 9 10 101012323 10 25 101012323 11 25 101012323 12 25 101012323 13 25 101012323 14 11 101012323 15 25 101012323Now what I want to do is return the top vPID instances from highest to lowest so my output should return 25, 11, 10 etc.I hope what I'm asking made sense to you guys, basically im just looking for the top 10 instances of a any product id (vPID) Thanks.
View 3 Replies
View Related
Sep 28, 2000
Hello All,
I am working on a ranking scenario for a client and I am stumped. Here's the situation...I have an aggregated table that has number of items sold for a salesperson for a given pay period. My client wants to the top 10 salespeople in a report. If two sales people tie, then they should have the same rank, and then the next salesperson in the ranking should have the appropriate number in the sort order. I am trying to figure out how to accomplish with the rank column below.
SalespersonID AmountSold Rank
1 5 1
2 5 1
3 4 3
4 4 3
5 2 5
I need the data in my table to look like the example and I am having a difficult time figuring out how. Any help would be appreciated.
Thanks,
Terry
View 1 Replies
View Related
Jan 17, 2005
My small number of brain cells prohibits me from figuring this out. I've got a table
ProviderName EOPCodeTotalDenied
Memorial Hosp66$4,598
Memorial HospA3$2,133
Memorial Hosp22$1,111
Memorial Hosp20$912
Memorial Hosp39$4,321
Memorial Hospb2$62
Parkdate Hospb2$6,251
Parkdate Hosp66$2,346
Parkdate Hosp22$1,252
Parkdate Hosp20$4,056
Stone HospV33$8,059
I need to output this table as below (with a denial rank for each hospital -- DESC sort on TotalDenied).
I'm stuck on how to get the DenialRank column to work.
ProviderName EOPCodeTotalDeniedDenialRank
Memorial Hosp66$4,5981
Memorial Hosp39$4,3212
Memorial HospA3$2,1333
Memorial Hosp22$1,1114
Memorial Hosp20$9125
Memorial Hospb2$626
Parkdate Hospb2$6,2511
Parkdate Hosp20$4,0562
Parkdate Hosp66$2,3463
Parkdate Hosp22$1,2524
Stone HospV33$8,0591
Appreciate the help.
Ray
View 5 Replies
View Related
Feb 14, 2008
Hi guys
I'm trying to figure out how to rank records. From the highest value to the lowest. Can you please help?
View 3 Replies
View Related
Mar 14, 2008
Hello all
Im working on a table in SQL server 2005 (sp2). A piece of that table is as below. I need a SQL code that adds a new column called rank that ranks starting from 0 by Tire_ID by date asc. For example, for Tire_ID = 41317 and 41350 the result will look like in the second table:
original:
Date
Tire_ID
Truck
Position
Area
10/5/1999 0:00
41317
T110
1
BAG
2/5/2000 0:00
41317
T109
1
BAG
5/9/2000 0:00
41317
T103
6
BAG
11/14/1999 0:00
41350
T109
2
BAG
4/19/2000 0:00
41350
T105
4
BAG
7/5/2000 0:00
41350
T104
4
BAG
9/3/2000 1:00
41350
T104
3
BAG
9/17/2000 1:00
41350
T115
5
BAG
Result( new)
Date
Tire_ID
Truck
Position
Area
rank
10/5/1999 0:00
41317
T110
1
BAG
0
2/5/2000 0:00
41317
T109
1
BAG
1
5/9/2000 0:00
41317
T103
6
BAG
2
11/14/1999 0:00
41350
T109
2
BAG
0
4/19/2000 0:00
41350
T105
4
BAG
1
7/5/2000 0:00
41350
T104
4
BAG
2
9/3/2000 1:00
41350
T104
3
BAG
3
9/17/2000 1:00
41350
T115
5
BAG
4
Thanks in advance
View 5 Replies
View Related
Oct 25, 2007
I have a sales report showing Customer, tons shipped, and sales amount with two parameters: year and month. I want to show the ranking by month by both tons and sales. It is easy enough to sort the results by one of the measures and compute a line number but I can't seem to get the rank function to work to compute the ranking by two different measures.
Any ideas?
Thanks.
View 1 Replies
View Related
Oct 16, 2007
I have a table that has several dozen account numbers. In the same table, each account number has several thousand control numbers (apparently the person who designed the DB never heard of Dr. Codd).
Anyway, I want to get the last five created control numbers for each account number. I have a "CreatedDateTime" field that I can use to order the control numbers by creation. I can use the Top and Rank functions to get the last five for an individual account number but I cannot seem to produce a solution with the last five for each account number.
Anyone have any ideas?
Thanks.
View 3 Replies
View Related
Oct 24, 2007
I'm building a standard search function for this project im working on and I wanted to know how could I do the following; Basically want I am using the standard "LIKE" function to search keywords but I also want to rank the returns based on how many matches. For example if someone uses the keywords "dog run park fast" and I have a couple of records that have certain amount combinations with these words, how would I return the one that has the most combinations to the least. If one record had "dog, run,park", another has "dog run" and the last would have "fast". How would I being those results in that order.Thanks for any help.
View 3 Replies
View Related
Nov 23, 2004
The problem is :
I have table :
--------------------------------
...User ... Speed(minute)...
Maria 0.8
John 0.5
Alan 0.8
Anne 2.0
Smith 1.0
Kate 1.5
Evan 1.5
--------------------------------
---> I wanna set ranking for them such as:
Rank.....User.....Speed(minutes)
1 John 0.5
2 Maria 0.8
2 Alan 0.8
4 Smith 1.0
5 Kate 1.5
5 Evan 1.5
7 Anne 2.0
---------------------------------
Anyone can tell me how?
Thanks much
View 2 Replies
View Related
Jul 28, 2006
A few weeks ago i posted this and got it to work perfectly. Now im trying to retreive the '3' closest stores rather than just the '1' closest store. How would I go about doing this? I tried using 'TOP 3' in the subquery but you can only retrieve one value.
Here is the origonal post:
"I have a table containing 100 stores, 5000 customers, and the distance between each store and each customer. I am trying to build a query that simply lists each customer and which store is closest and its distance.
Right now it looks like this:
Customer1|store1|33
Customer1|store2|15
Customer1|store3|28
Customer1|store4|35
Customer2|store1|35
Customer2|store2|95
Customer2|store3|28
Customer2|store4|76
I want to get it like this:
Customer1|store2|15
Customer2|store3|28"
Here is the solution:
Code:
SELECT CustomerPost, StorePost, StoreName, Distance
FROM Customer_Store_Distance2 as T
WHERE (Distance =
(SELECT MIN(Distance)
FROM Customer_Store_Distance2
WHERE CustomerPost = T.CustomerPost))
View 10 Replies
View Related
Feb 14, 2005
I have query that calculate percentage. i need to rank it to nearest 2 decimal places
query like
SELECT distinct [table a]/[table b] *100 as percentage
from blabla
results
.........
2.7777
20.125
..
want to get result
3
20
View 4 Replies
View Related
Sep 4, 2015
how I could achieve a ranking number, where I have some grouping involved.I want to have the groups in sets of 4, but if I get to a new person the ranking increases, even if I haven't filled the group. For example:
Name Year Sales Ranking
-------------------- ----------- ----------- --------------------
John Smith 2009 1296 1
John Smith 2010 1296 1
John Smith 2011 1296 1
John Smith 2012 1296 1
John Smith 2013 1296 2
[code]....
So, when I get to John's 5 year, the Rank increased. I then only had two more rows before I get to Nina, but the Rank number goes up, as the details are for a different person from the prior user.
Select *, DENSE_RANK() over (Order by Name) + ((RANK() over (Partition by Name Order by Year) - 1) /4) as Ranking
from #Test
Order by Name, Year
Drop Table #TestThis works for John, but Nina then has a Ranking of 2 for her only year, where as she should be 3. Steve also then has a value one too low for all of his ranks.
View 3 Replies
View Related
Dec 14, 2007
I have a rank column and a column with percents. The higher the percent the better the rank. How would I do this?
View 1 Replies
View Related
Mar 20, 2008
I have tried to get answers on this before, but haven't had any luck. I am working on trying to finish a report using SSRS in Visual Studio.
The report is a Sales report. I have the report grouped by Location of our Offices and their Customers grouped underneath. The Customers revenue is Summed on the Customers group footer. I have this sorted by Top customer (highest revenue) to lowest customer (lowest revenue). This is fine and great, but now I need to limit the number of Customers (there could be upwards of 100 or more per Office) to the Top 20.
I would like to do this on the report side, but if I need to do it in my SQL query I will.
I would like to get the row number for the Customer group footer. Then I can limit these <=20, but when trying on the report side doing
=rownumber(Customer)
I get an aggregate function error for incorrect Scope.
Any help would definately be appreciated. I have been stuck for about a week now trying to figure this out.
View 4 Replies
View Related
Jun 30, 2006
Hi,
Why adidas is better-ranked than nike??
Table "indice":
idcCod fabCod mcaCod catCod fabNom mcaNom catNom
1111NikeNikeRopa
2221AdidasAdidasRopa
3113NikeNikeMedias
4118NikeNikeLargas
SELECT[RANK] ,[KEY],idc.*
FROMFREETEXTTABL (dbo.indice,mcaNombre,fabNombre,catNombre),
'ropa adidas nike',
LANGUAGE 3082, 5) res left join
dbo.indice idc with (nolock) on idc.idcCodigo=res.[key]
ORDER BY [RANK] DESC;
Result:
RANK KEY
182
03
04
01
Why idcCods's 1 and 2 have different rank??
because idcCod's 3 and 4 affects ?? or it's just thinks of language??
Thank's in advance
View 5 Replies
View Related
Oct 14, 2005
Ok. Im not able to understand this logic please help. As you can see we have 2 columns of ranks, 1)normal 2)corrective. what is the logic behind this and how do u write a query for this? these ranks are for the Salary Column.
Imran,
"You truly do not know someone untill you fight them."-THE MATRIX.
EmpID Empname EmpSalary RankNormal RankCorrective
1 A 150001 1
2 B 100002 4
3 C 150001 1
4 D 40003 5
5 E 150001 1
6 F 15004 6
7 G 15004 6
8 H 5005 8
View 20 Replies
View Related
Aug 17, 2006
I have table :
Result1 Rank
5
6
78
4
27
3
How to rank in above table ?
Thank you very much !
View 13 Replies
View Related
May 28, 2007
Hi All,
Please let me know the equivalent of RANK() over ( order by...)
in SQL server 2000.
I thought this was supported in SQL server 2000.
Please let me know if there exists a user defined function.
Thanks in Advance.
Thanks
View 1 Replies
View Related
