Rank Function

Jan 8, 2008

Is there a way to use the Rank function like in 2005 for sql 2000

View 6 Replies


ADVERTISEMENT

Rank Function

Jul 30, 2007

Hi ,

I have a report created and within the report is columns that perform additions on the fields from the database.

I want to create a rank column to show the rank of the row compare with the rest.

Col1 Col2 Total Ranks
1 2 3 3
2 3 5 1
2 2 4 2

On the above Col 1 and 2 would come form my database, Total is calculated by the report. How can I get ranking on this total? I cannot sort by the total as the report should only showing rankings but not be ordered by the rank.

I am using SQL Server 2005 Reporting Services...anny help is much appreciated.

Thanks

View 8 Replies View Related

SQL Server 2012 :: Incorporate RANK Or NTILE With LEAD Function?

Jan 9, 2014

We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:

if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go

-- Create temp tables for sample data

create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)

-- Insert some sample data

insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')

-- Use lead function to determine how many months are between

-- consecutive dates per account

; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval

where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.

-- Show the results

select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis

Results (this is not getting me what I'm looking for):

RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122

[code]....

This is what I'm trying to achieve:

RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114

[code]....

The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.

I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.

View 5 Replies View Related

SQL Server 2008 :: Rank Function Taking Care Of Gap Between Days

May 24, 2015

Working on sqlserver 2008 R2

CREATE TABLE OrderRanking
(
OrderID INT IDENTITY(1,1) NOT NULL,
CustomerID INT,
OrderDate date
)
INSERT OrderRanking (CustomerID, OrderDate)

[Code] ...

Looks fine but what I need is DRP with this:

CustomerID OrderDate 'DRP taking care of the gap in the days'
1 '01-01-2015' 1
1 '01-01-2015' 1
2 '02-01-2015' 1
2 '02-01-2015' 1
2 '05-01-2015' 4
2 '05-01-2015' 4

View 2 Replies View Related

About FTS Rank

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

Rank This!!...

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

Rank In Sql ?

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

Rank() Over

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

How To Get Rank?

Jun 28, 2006

I would like to write a query that gives me the values of a set ofobservations, and their rank.CREATE TABLE #Values(val int)INSERT #Values SELECT 1INSERT #Values SELECT 5INSERT #Values SELECT 10I would like to select this:1 10 -- rank 1, value 102 53 1I can put them into a temp table with an identity column, ordered bythe column I'm interested in, and then retrieve in order by theidentity column. I'm wondering if there's a way to do that with asubquery.Thanks,Jim

View 3 Replies View Related

Using Rank

May 22, 2006

I've written a bunch of code using contains for fts. Then as I was trying to run the sorting, I realized that I have to use containstable in order to sort by rank. Is that correct? When I was using contains, I just used it as a where clause so I would have something like...

WHERE ((PostedUntil >= '5/22/2006') AND (SiteId=199)) AND (CONTAINS (PositionTitle, '"sales"') OR CONTAINS (Description, '"sales"'))

From the examples I've seen, in order to use containstable, I have to join a new dynamic table to the freetext enabled table and it only uses 1 containstable phrase for the new table. In my case, I may have multiple containstable phrases, so would they all fall in a () set like..

FROM Categories AS FT_TBL INNER JOIN
(CONTAINSTABLE (table, col1, searchphrase) OR (CONTAINSTABLE(table, col2, searchphrase2) OR CONTAINSTABLE (table, col3, searchphrase3)) AS KEY_TBL

... or would each containstable have to be a new join? I don't want to spend anymore time going back and rewriting code just to test it since it's about a days worth of recoding. Thanks.

View 3 Replies View Related

SQL Member Rank

May 13, 2008

ive created a photo sharing site, and im trying to show the statisics for certain users, so far i can COUNT all the photos the user has upload, and show them in descending order, but i need to be able to see the ranking of the user, like :

RANK USERID No. Of Photos

1 10 100
2 8 70
3 9 85


is there anyway of doing this? thanks Si!

View 7 Replies View Related

Sql 2000 Rank

Aug 1, 2006

My data:


Code:


CUST NO | StoreName| Rank
Cust1 0781 1
Cust1 0246 2
Cust1 0481 3
Cust2 2101 1
Cust2 8876 2
Cust2 5445 3
Cust3 3243 1
Cust3 4545 2
Cust3 3223 3



Im trying to find a way to rank as shown in the third column.

This is the code im using:


Code:


SELECT top 100 (CustomerNo)as CustomerNo, StoreName,

(SELECT COUNT(DISTINCT CustomerNo)
FROM dbo.Top3CustomerNoStores AS O2
WHERE O2.CustomerNo < O1.CustomerNo) + 1 AS drnk


FROM Top3CustomerNoStores as O1



... and getting this result


Code:


CUST NO | StoreName| drnk
Cust1 0781 1
Cust1 0246 1
Cust1 0481 1
Cust2 2101 2
Cust2 8876 2
Cust2 5445 2
Cust3 3243 3
Cust3 4545 3
Cust3 3223 3



... and using this code


Code:


SELECT top 100 (CustomerNo)as CustomerNo, StoreName,
(SELECT COUNT(*)
FROM dbo.Orders AS O2
WHERE O2.qty < O1.qty) + 1 AS rnk




.... which gives me ...


Code:


CUST NO | StoreName| rnk
Cust1 0781 1
Cust1 0246 1
Cust1 0481 1
Cust2 2101 4
Cust2 8876 4
Cust2 5445 4
Cust3 3243 7
Cust3 4545 7
Cust3 3223 7



I have noticed in SQL 2005, this is accomplished much easier with built in functions.

any help appreciated

View 1 Replies View Related

Rank Equation

Jun 7, 2006

Hi all,

until recently ive been using a rank equation to calculate rank,

essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:

((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank

problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?

here is the full statement :

SELECT StudentId, GCSE_Score, LTRIM(STR
((SELECT COUNT(*)
FROM dbo.[Score2004-05]
WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId
FROM dbo.[Score2004-05] S
WHERE (SetId = '2004/2005')

greg

View 4 Replies View Related

Rank In FREETEXTABLE

Apr 8, 2008

I need help in understanding how the rank is calculated in FREETEXTABLE. I have a following query

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]

First, the resulting rows does have one record which has an address 80 ridge avenue but it appears at 15th place. Ideally it should appear on 1st. All the ranks of the results are same.

Second, the results are also showing two rows which does not contain the specified search string at all. They not only appears above in the resulting table but also have the same rank as the result in question (80 ridge avenue). For example result shows €œLong Ridges, Flat 21, Fortis Green€? at fifth place which is no way near the search string. And actual record shows up at 15th Place

Is there any way we can influence the rank to show exact match first. Just to clarify I have removed the digits from noise file as we need to search house numbers. Digits appears in noise file as default.

View 1 Replies View Related

I Need Help With A RANK() Issue

Mar 15, 2008

This query:

SELECT xx.JumpHeight, xx.HeightRank, xx.NoEvents, xx.MinRunDate, xx.DogIdent,

Dogs.CallName, DogOwner.LastName, DogOwner.FirstName

FROM

(SELECT JumpHeight, DogIdent, MIN(RunDate) as "MinRunDate", COUNT(Event_ID) AS "NoEvents",

RANK() OVER (PARTITION BY JumpHeight ORDER BY COUNT(Event_ID) DESC, MIN(RunDate)) AS "HeightRank"

FROM EventData

WHERE Event=@Event

GROUP BY JumpHeight, DogIdent) AS xx, Dogs, DogOwner

WHERE (Dogs.Breed = @Breed AND xx.DogIdent = Dogs.DogIdent and Dogs.Owner_ID = DogOwner.Owner_ID) AND

(xx.HeightRank <= 10)

ORDER BY xx.JumpHeight, xx.HeightRank


produces this output:





Jump Ht.

Rank

# of Events

First Event

Owner

Call Name


08

3

1

2/19/2006

Some Owner

Otto


08

4

1

3/12/2006

Some Owner

Schotzie

I want it to produce this output:





Jump Ht.

Rank

# of Events

First Event

Owner

Call Name


08

1

1

2/19/2006

Some Owner

Otto


08

2

1

3/12/2006

Some Owner

Schotzie

I have tried several things and cannot correct the problem. Obviously, RANK is being evaluated in the wrong place, but placing it elsewhere has failed to produce the above results.

I appreciate any help I can get. Thanks!

View 4 Replies View Related

How To Get Rank Column In Sql 2000

Dec 20, 2007

Hi,All,
I have one table like this
UserID,Name,GameScore
1         A   25
2         B   23
3        C   22
4        D  25
5        E  23
6        F  26
Now i want the query which return like this
Name   Score   Rank
F         26        1
A         25        2
D         25        2
B         23        3
E         23        3
C         22       4
Can anyone give me the sql 2000 query for this

View 4 Replies View Related

SubQuery - Rank Of Rows

Mar 16, 2005

Hi all:

In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:

Spend Rank
24 1
12 2
10 3
9 4

What I wish to accomplish is:

Spend Rank (descending)
24 4
12 3
10 2
9 1


Please let me know what I need to accomplish it.

Thanks for the help

Victor

SELECT
CompanyName, Spend,
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)
AS Rank
FROM
ACC0704
ORDER BY
Spend DESC

View 7 Replies View Related

Order / Rank Results

Feb 8, 2006

Hi,

i want to create a report so that a list of the top 30 records are returned to the report user. In the report i want to have the records position in the list shown (ie the first row should have 1. and the second should be 2. right on down to the 30th having 30.)

how do i achieve this please?


many thanks
FatherJack

View 1 Replies View Related

Assinging A Rank To A Record

Nov 23, 2007

Hi all,

I don't know how to explain what I wanna do in english so here's a concrete example:

I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2

And I wanna do something like this
Item - Rank - Qty
IT1 - 1 - 2
IT1 - 2 - 4
IT1 - 3 - 5
IT2 - 1 - 2
IT2 - 2 - 2

So basically I want to assing a rank (on the fly) in a SELECT statement

Thanks in advance

Or Tho

View 10 Replies View Related

Create Rank Formula

Aug 1, 2012

i am trying to create a rank formula.i just need to return the highest MCC count for the mcc code and return the seller code...So eg row 3 and 4, there are two MMC CODE called 4772, i need the formula to return the seller code r10, this is because r10 has 9 counts against that mcc code, and r03 has 9,

View 2 Replies View Related

Adding Time To Rank

Feb 27, 2004

Using FoodMart, how can i add time to this:

WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM Sales

View 2 Replies View Related

RANK Get Previous Record But Not Always

Apr 16, 2014

I need to get the previous price for all my PROMOTION records but not when the previous record is a type PROMOTION also it needs to keep going back to get the price.

I have created a table with RANK in which works OK to get previous price for all but how can I say if previous price is type PROMOTION go to next previous prices...

Bets way to show an example is with a jpeg image I have but having trouble inserting into this message...

SELECT a.[StartPrice]
,a.[ProductID]
,a.[Colour]
,ISNULL(b.[Price],a.[Price]) AS [Price Before]
,a.[Price] AS [Promotion Price]

[Code] ...

Table

PriceCodeStartPriceEndPriceProductIDColourPriceRank
RETAIL21-Oct-1324-Dec-13Bike15BLUE39.001
PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.202
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.203
MARKDOWN25-Dec-1314-Jan-14Bike15BLUE31.204

[Code] ....

Want I'm trying to do select PriceCode PROMOTION and get previous price:

Get previous price
PROMOTION29-Nov-1301-Dec-13Wheel1BLACK31.2039.00

But in this example it picks up the PROMOTION before and I need to ignore this and get rank 1 price for both

PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.2039.00
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.2039.00

View 8 Replies View Related

How To Create Rank Column

Jun 30, 2005

If I have a column like this

View 9 Replies View Related

Rank() And Using WHERE Clause On Results

Oct 25, 2006

Hi,

I am trying to return the 100th ranking in my SQL, ie

SELECT DailyValueChange, BUSINESS_DATE, RANK() OVER (order by DailyValueChange) AS RANK_Vals

FROM Table

WHERE (BUSINESS_DATE = @CurrentBusDate) AND (RANK_Vals = 100)

However when I try to update the Stored Procedure it tells me RANK_Vals is an invalid column name, which is not the case as if I run it without the Where clase it runs and returns all results.

Any advice on how to get around this would be greatly appreciated.

Cheers

Mark

View 4 Replies View Related

Rank Beginner Does Not Know How To Get Started

Sep 14, 2007

Hi - Can someone point me to a single document that tells me how to get started from nothing with SQL Server 2005 Express? I keep getting pointed to lists of many documents, none of which does what I specifically need. They all seem to have many general instructions, but I just want to get started with some simple database that I load with a bit of data and then perform some queries.

I have ordered a book from Amazon but don't have much hope that it will do what I need.

Doug

View 4 Replies View Related

Transact SQL :: RANK - Using To Take Only Changes For One Column

Nov 8, 2015

I have a table that has the definition from the picture. Let's suppose I will sort the list based on last column (DATA.ORA - DESC). That means the last row inserted is the first one in the sorted list.

What I would like to do is to get only the records when the value of a specific column is different from the row below.

For instance I would like to get the rows where the column WHITE has different values. When the status for column WHITE changes from 0 to 1 or from 1 to 2 etc., then I need that row no matter if next 5 rows has the same status (1).

In the picture you see what the result of query should be:

Result: only the lines where the value of column WHITE is different than the row below.

View 6 Replies View Related

Get The Rank Of Returned Rows (was SQL Question)

Jan 11, 2005

Given the following results:
col0 col1 col2
THY 2,265,850 31
VIE 1,474,994 20
RID 1,221,800 17
ACC 1,124,335 15
FEI 445,184 6
DIR 433,783 6
ROM 324,365 4

What is the best way in a query to get the rank of the returned rows by either col1 or col2. In other words who's the number 1,2,3 etc...

total count col0 = 7
total col1 = 7,290,310
total col2 (would eqaul 100%)= 99%

Looking for a mathmatical solution to this any help would be appreciated.

View 11 Replies View Related

Rank/order Derivation Question

Dec 2, 2005

OOPS Sudden inspiration - got it now


Guys
Spent all morning on this - hope someone can help
I've got some select code :


declare @BusinessDate DATETIME
set @BusinessDate = '28 Feb 2005'

select
--?? as period
tdbe.eventid , tdbe.tradeid ,tdbe.cashflowstartdate
from HDD_t_HA HA
join hdd_t_Hedge_Instrument HI on HA.HAId = HI.HAId
join hdd_t_TDB_Event TDBE on HI.EventId = TDBE.EventId
join hdd_t_TDB TDB on TDBE.TradeId = TDB.TradeId
where ha.haid = 29 and
ha.validfrom <= getdate() and ha.validto > getdate()
and tdbe.effectivefrom <= @BusinessDate and tdbe.effectiveto > @BusinessDate
and tdb.effectivefrom <= @BusinessDate and tdb.effectiveto > @BusinessDate
--and tdbe.tradeid = 117
order by tdbe.tradeid , cashflowstartdate


Which produces :
eventid tradeid cashflowstartdate
121102004-04-19 00:00:00.000
122102004-07-19 00:00:00.000
123102004-10-19 00:00:00.000
124102005-01-19 00:00:00.000
125102005-04-19 00:00:00.000
126102005-07-19 00:00:00.000
127102005-10-19 00:00:00.000
128102006-01-19 00:00:00.000
68101172004-11-16 00:00:00.000
68111172005-05-16 00:00:00.000
68121172005-11-16 00:00:00.000
68131172006-05-16 00:00:00.000
68141172006-11-16 00:00:00.000
68151172007-05-16 00:00:00.000
68161172007-11-16 00:00:00.000
68171172008-05-16 00:00:00.000
68181172008-11-16 00:00:00.000
68191172009-05-16 00:00:00.000
68201172009-11-16 00:00:00.000
68211172010-05-16 00:00:00.000
68221172010-11-16 00:00:00.000
68231172011-05-16 00:00:00.000

I'm trying to derive a 'period' value which is an integer based on the ordering of cashflowstartdatefor each tradeid group. The desired effect is as below

Period eventid tradeid cashflowstartdate
1 121102004-04-19 00:00:00.000
2 122102004-07-19 00:00:00.000
3 123102004-10-19 00:00:00.000
4 124102005-01-19 00:00:00.000
5 125102005-04-19 00:00:00.000
6 126102005-07-19 00:00:00.000
7 127102005-10-19 00:00:00.000
8 128102006-01-19 00:00:00.000
1 68101172004-11-16 00:00:00.000
2 68111172005-05-16 00:00:00.000
3 68121172005-11-16 00:00:00.000
4 68131172006-05-16 00:00:00.000
5 68141172006-11-16 00:00:00.000
6 68151172007-05-16 00:00:00.000
7 68161172007-11-16 00:00:00.000
8 68171172008-05-16 00:00:00.000
9 68181172008-11-16 00:00:00.000
10 68191172009-05-16 00:00:00.000
11 68201172009-11-16 00:00:00.000
12 68211172010-05-16 00:00:00.000
13 68221172010-11-16 00:00:00.000
14 68231172011-05-16 00:00:00.000

Hope this makes and thanks in advance

View 1 Replies View Related

Need To Count And Rank NVARCHAR Column

Apr 2, 2008

Assuming that I have a table similar to this simplified version:

CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));

How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:

field1 count
some value 24
another word 20
a value 5

(and so on...)

How can I do this?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

View 3 Replies View Related

Data Load Based On Rank

Aug 9, 2007

I'm fairly new to SQL and I have been asked to load multiple tables with duplicate and inconsistent data into a single table. Each source (table) has been assigned a rank in terms of the quality and reliability. After some thought, I have come up with this solution to get all of the data loaded based on rank.

Does anyone know of a better way to do this?

Value is a unique key.

SELECT *
FROM table_1

union

SELECT *
FROM table_2
where (value + '_' + yr) in
( select value + '_' + yr
from table_2
except
select value + '_' + yr
from table_1)

union

SELECT *
FROM table_3
where (value + '_' + yr) in
( select value + '_' + yr
from table_3
except
select value + '_' + yr
from table_2
except
select value + '_' + yr
from table_1)

View 1 Replies View Related

Create A Rank Column In A View

Jan 14, 2008

Hi all!

I'm trying to script a view that does a simple query to some tables.
The catch is 2 of the columns are created on run time.
The first column is filled with a calculation with values of other columns. The 2nd column I would like to fill with the ranking of this calculated column

Example: (imagine caculatedcol is Sum(Col1+Col2+Col3)

Col1 Col2 Col3 CalculatedCol Ranking
10 10 10 30 1
9 9 9 27 2
8 8 8 24 3
7 7 7 21 4

How can I get the Ranking column filled based on the calculated column?

I'm desperate.
Thanks for any help.


---
Mário Ramos

View 8 Replies View Related

How To Rank An Union Of Two Result Sets?

Mar 1, 2007

I am new to SQL Server Integration Services. I need to help here.

If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it.

Does anyone know how to achieve this?

Thanks!

View 3 Replies View Related

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 View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved