Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans

Find text in collapsed drilldown report

I have a drilldown report. Without opening a drilldown item, is there a way
to search for a text string using the "Find" link when viewing a report? If
not, what would be the best recommendation to accomplish this.Hello Parker,
Without opening the drilldown item, you could not use the "Find" button to
find the text in the drilldown item.
This is by design because the find operation only search the HTML which is
appearanced.
My suggestion is that add a parameter in the report to control to expanded
all the items.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Find start and end date strings

Hello,

I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:

Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04

The results I am looking for would be as follows:

Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04

I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??

Thanks in advance!!bump..any one have ideas??|||How about something like this?

Declare @.sCustomer as varchar(5)
Declare @.dtStartDate as datetime
Declare @.dtEndDate as Datetime
Declare @.sSaveCustomer as varchar(5)
Declare @.dtSaveStartDate as DateTime
Declare @.dtLinkDate as DateTime

DECLARE Test CURSOR FOR
SELECT * FROM Test ORDER BY Customer, StartDate, EndDate

CREATE table #tmp (Customer varchar(5), StartDate DateTime, EndDate DateTime)

OPEN TEST

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate

SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate

if (@.@.FETCH_STATUS = 0)
BEGIN
Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if ((@.sSaveCustomer = @.sCustomer) AND (@.dtStartDate = @.dtLinkDate))
BEGIN
SET @.dtLinkDate = @.dtEndDate
END
ELSE
BEGIN
INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)
SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate
END

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)

select * from #tmp

Drop table #tmp

Close Test
Deallocate Test

Have some fun.|||A similar question was asked in this thread.

http://www.dbforums.com/t1005647.html|||Thanks!! Both options worked!!

Find similar items

Hello!
I need to find the best way to find items that are similar to each
other depending on several attributes.
Each item has 100 attributes with a numeric (float) value between 0 and
100.
Items that are similar have the least difference attribute value for
all 100 attribtes.
So what we need to do is calculate the difference (of all attributes)
between the item that we want to find similiarities of and the other
items in the database. But we cannot use SUM() over all attributes
because we need to do some calculation on most (if not all) attributes.
For example, if the difference is more than 20 we should consider that
as the item does not match very well on that attribute, therefore
return 0 (zero). If the difference is less then 5 we consider that as
very good match and therefore return 100 points in similiary. If its
between 5 and 20 then we return a score based on a formula we have.
We will also need to group these items into categories. For example
category1 can be calculated like
(ScoreOfAttribute1+ScoreOfAttribute2+Sco
reOfAttribute8)/3. We divide by
3 since there are 3 attributes in that category.
I have tried a couple of ways to implement this.
Example one:
Items
- ItemId
- Name
- Price
Attributes
- AttributeId
- Description
AttributeValues
- ItemId
- AttributeId
- AttributeValue
Example two:
Items
- ItemId
- Name
- Price
AttributeValue
- AttributeId
- Attribute1Value
- Attribute2Value
- .
- Attribute100Value
I have also carefully choosen the indexes and most of the time they
will be used but there will always be some table scan since we need to
do calculations to find the items with most simlarity, based on my
"similary calculations".
So now I need to get some new ideas on how to solve this problem.
I have been thinking of a table with precalculated points but that
table will probably get very big. We have over 150000 items that will
need to be compared with each other.
I have been thinking if it would be possible to sum all attribute
values and store that and then divide by the number of attributes, and
then we could just calculate the difference of the best 500 hits and
get the top 100 from those? This will probably be quicker but it may
also result in faults, since we cannot be sure that the most similar
item is within this top 500.
So if you have any ideas on this problem please write!
The table design can be changed. The performance on the SELECT is the
most important and the UPDATE/DELETE/INSERT is acceptable to take some
time.
What we are looking for here is to get the result (top 100 most similar
items (out of 150000 items with 100 attributes)) within 1 second.Post some sample data and the result you want
Madhivanan|||Please post DDL and maybe some insert statements for sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
Just to take a wild guess, lets imagine the following table.
(Note: I created 3 views below just to break up the logic into simple chunks
that are easier to alter/discuss. There is no reason why this would not be
all in one step):
create table Products
(
ProductID integer primary key not null
, Attrib1 integer
, Attrib2 integer
,constraint Products_Attrib1 check (Attrib1 between 0 and 100)
,constraint Products_Attrib2 check (Attrib2 between 0 and 100)
)
;
go
insert into products (ProductID, Attrib1, Attrib2) values(1,12,34);
insert into products (ProductID, Attrib1, Attrib2) values(2,34,45);
insert into products (ProductID, Attrib1, Attrib2) values(3,75,98);
insert into products (ProductID, Attrib1, Attrib2) values(4,98,26);
insert into products (ProductID, Attrib1, Attrib2) values(5,56,44);
insert into products (ProductID, Attrib1, Attrib2) values(6,95,23);
go
/*
View to cross reference all products and retrieve the difference between
individual attributes
*/
Create view ProductXref as
Select a.productID as Product1
, b.productID as Product2
, abs(A.Attrib1 - b.Attrib1) as Score1
, abs(A.Attrib2 - b.Attrib2) as Score2
from Products A
cross join Products B
where A.ProductID <> B.ProductID
go
/*
View to cross calculate individual scores per attribute
arbitrary function used when score between 5 and 20
*/
Create view AttributeScores as
Select Product1
, Product2
,(Case when Score1 < 5 then 100
when Score1 between 5 and 20
Then 100-(score1*5)
Else 0 End) as Score1
,(Case when Score2 < 5 then 100
when Score2 between 5 and 20
Then 100-(score2*5)
Else 0 End) as Score2
from ProductXref
go
/*
View to calculate final scores.
Also returns individual scores for comparison purposes
*/
create View XrefScoring as
Select Product1
, Product2
, Score1
, Score2
, (Score1 + Score2)/2 as AvgScore
from AttributeScores
go
/*
select final results
*/
select Product1
, Product2
, Score1
, Score2
, AvgScore
from XrefScoring
where AvgScore > 0
and Product1 = 4
"Patrik" <corneliusson@.gmail.com> wrote in message
news:1148889857.588763.316200@.j55g2000cwa.googlegroups.com...
> Hello!
> I need to find the best way to find items that are similar to each
> other depending on several attributes.
> Each item has 100 attributes with a numeric (float) value between 0 and
> 100.
> Items that are similar have the least difference attribute value for
> all 100 attribtes.
> So what we need to do is calculate the difference (of all attributes)
> between the item that we want to find similiarities of and the other
> items in the database. But we cannot use SUM() over all attributes
> because we need to do some calculation on most (if not all) attributes.
> For example, if the difference is more than 20 we should consider that
> as the item does not match very well on that attribute, therefore
> return 0 (zero). If the difference is less then 5 we consider that as
> very good match and therefore return 100 points in similiary. If its
> between 5 and 20 then we return a score based on a formula we have.
> We will also need to group these items into categories. For example
> category1 can be calculated like
> (ScoreOfAttribute1+ScoreOfAttribute2+Sco
reOfAttribute8)/3. We divide by
> 3 since there are 3 attributes in that category.
> I have tried a couple of ways to implement this.
> Example one:
> Items
> - ItemId
> - Name
> - Price
> Attributes
> - AttributeId
> - Description
> AttributeValues
> - ItemId
> - AttributeId
> - AttributeValue
>
> Example two:
> Items
> - ItemId
> - Name
> - Price
> AttributeValue
> - AttributeId
> - Attribute1Value
> - Attribute2Value
> - .
> - Attribute100Value
> I have also carefully choosen the indexes and most of the time they
> will be used but there will always be some table scan since we need to
> do calculations to find the items with most simlarity, based on my
> "similary calculations".
> So now I need to get some new ideas on how to solve this problem.
> I have been thinking of a table with precalculated points but that
> table will probably get very big. We have over 150000 items that will
> need to be compared with each other.
> I have been thinking if it would be possible to sum all attribute
> values and store that and then divide by the number of attributes, and
> then we could just calculate the difference of the best 500 hits and
> get the top 100 from those? This will probably be quicker but it may
> also result in faults, since we cannot be sure that the most similar
> item is within this top 500.
> So if you have any ideas on this problem please write!
> The table design can be changed. The performance on the SELECT is the
> most important and the UPDATE/DELETE/INSERT is acceptable to take some
> time.
> What we are looking for here is to get the result (top 100 most similar
> items (out of 150000 items with 100 attributes)) within 1 second.
>|||You got everything right (except that we use decimal(5,2) instead of
integers for the values of the attributes), so I dont think there is no
need for me to write more example code.
Do you think its possible to speed up this query some more? Maybe make
a clustered view or something?
Got any ideas?|||You improve this a bit with a CASE expression done at the same time as
the cross join.
SELECT P1.product_id, P2.product_id,
CASE WHEN ABS(P1.a1 - P2.a1) <= 5
THEN ABS(P1.a1 - P2.a1) ELSE 999.99 END
+ CASE WHEN ABS(P1.a2 - P2.a2) <= 5
THEN ABS(P1.a2 - P2.a2) ELSE 999.99 END
.
+ CASE WHEN ABS(P1.a100 - P2.a100) <= 5
THEN ABS(P1.a100 - P2.a100) ELSE 999.99 END
AS score
FROM Products AS P1, Products AS P2
WHERE P1.product_id <> P2.product_id
AND CASE WHEN ABS(P1.a1 - P2.a1) <= 5
THEN ABS(P1.a1 - P2.a1) ELSE 999.99 END
+ CASE WHEN ABS(P1.a2 - P2.a2) <= 5
THEN ABS(P1.a2 - P2.a2) ELSE 999.99 END
.
+ CASE WHEN ABS(P1.a100 - P2.a100) <= 5
THEN ABS(P1.a100 - P2.a100) ELSE 999.99 END
< @.magic score;|||Yup, you would probably do them at the same time, but I did it in more than
one step just to make it easier to follow. Where math is concerned I prefer
to break things down into smaller pieces for illustration purposes. It
makes my head hurt less.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149030171.071254.57080@.i40g2000cwc.googlegroups.com...
> You improve this a bit with a CASE expression done at the same time as
> the cross join.
> SELECT P1.product_id, P2.product_id,
> CASE WHEN ABS(P1.a1 - P2.a1) <= 5
> THEN ABS(P1.a1 - P2.a1) ELSE 999.99 END
> + CASE WHEN ABS(P1.a2 - P2.a2) <= 5
> THEN ABS(P1.a2 - P2.a2) ELSE 999.99 END
> ..
> + CASE WHEN ABS(P1.a100 - P2.a100) <= 5
> THEN ABS(P1.a100 - P2.a100) ELSE 999.99 END
> AS score
> FROM Products AS P1, Products AS P2
> WHERE P1.product_id <> P2.product_id
> AND CASE WHEN ABS(P1.a1 - P2.a1) <= 5
> THEN ABS(P1.a1 - P2.a1) ELSE 999.99 END
> + CASE WHEN ABS(P1.a2 - P2.a2) <= 5
> THEN ABS(P1.a2 - P2.a2) ELSE 999.99 END
> ..
> + CASE WHEN ABS(P1.a100 - P2.a100) <= 5
> THEN ABS(P1.a100 - P2.a100) ELSE 999.99 END
> < @.magic score;
>|||What are the indexes you have at the moment?
How long is the query taking to run?
Will you always be looking for comparisons for a single product?
I don't think we can create an indexed view because the view contains a self
join. I don't know if this is a problem in SQL 2005 or not, but it is in
2000. If you need it to be really fast, you could create a reporting table
that is updated every so often (wly, daily, hourly, etc) but I would use
that only as a last resort.
"Patrik" <corneliusson@.gmail.com> wrote in message
news:1149024530.832795.257200@.j55g2000cwa.googlegroups.com...
> You got everything right (except that we use decimal(5,2) instead of
> integers for the values of the attributes), so I dont think there is no
> need for me to write more example code.
> Do you think its possible to speed up this query some more? Maybe make
> a clustered view or something?
> Got any ideas?
>|||Right now since we have a table that looks like this.
ProductId
Attribute1
Attribute2
...
Attribute100
There is only one index (clustered index) on the ProductId column.
Yes I will always compare just a single product against the others.
When the scope of items are 1000 the query takes <2 seconds. When the
scope of items are 13000 the query takes 14 seconds.
Jim Underwood wrote:
> What are the indexes you have at the moment?
> How long is the query taking to run?
> Will you always be looking for comparisons for a single product?
> I don't think we can create an indexed view because the view contains a se
lf
> join. I don't know if this is a problem in SQL 2005 or not, but it is in
> 2000. If you need it to be really fast, you could create a reporting tabl
e
> that is updated every so often (wly, daily, hourly, etc) but I would us
e
> that only as a last resort.
> "Patrik" <corneliusson@.gmail.com> wrote in message
> news:1149024530.832795.257200@.j55g2000cwa.googlegroups.com...|||"Patrik" <corneliusson@.gmail.com> wrote in message
news:1149111797.665216.285050@.i40g2000cwc.googlegroups.com...
> When the scope of items are 1000 the query takes <2 seconds. When the
> scope of items are 13000 the query takes 14 seconds.
You mean if you have 13000 rows in your table the query takes 14 seconds to
find all the matches for one product? This seems like an awfully long time
for 13000 records. In this case, since we are checking every record, we
can't make use of the index, but I would expect 13000 records to be
processed in a second or two.
I tested on my system with 15000 products (100 attributes each) and was able
to get results back in about 2 seconds. I am running SQL Server 2000 on a
PC with Windows XP installed. The pc has 1 gig of ram and one 3.4 Ghz
processor. Several other services are installed and running on the machine,
so it is not a dedicated db server.|||Maybe it takes longer because I do alot of calculations?
For example:
((((Score16+Score17+Score18)/3)/2) + (Score5/2))*0.25 As Something
Jim Underwood skrev:

> "Patrik" <corneliusson@.gmail.com> wrote in message
> news:1149111797.665216.285050@.i40g2000cwc.googlegroups.com...
> You mean if you have 13000 rows in your table the query takes 14 seconds t
o
> find all the matches for one product? This seems like an awfully long tim
e
> for 13000 records. In this case, since we are checking every record, we
> can't make use of the index, but I would expect 13000 records to be
> processed in a second or two.
> I tested on my system with 15000 products (100 attributes each) and was ab
le
> to get results back in about 2 seconds. I am running SQL Server 2000 on a
> PC with Windows XP installed. The pc has 1 gig of ram and one 3.4 Ghz
> processor. Several other services are installed and running on the machin
e,
> so it is not a dedicated db server.

Find how an item ranks in a table?

Is it possible to find the position of an item without retrieving all the records in the table?

I have a products table, and want to find out how its sales are doing within a particular category.

My table consists of the following columns of interest:

ProductID, CategoryID, ItemsSold.

So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?

SELECT *FROM dbo.ProductsWHERE CategoryID = 10ORDER BY ItemsSold

Also, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?

Any help would be very much appreciated.

Thanks.

The only way I can see of doing this is by creating a store procedure that returns the required values. Note that the only part I am not sure about is if the insert will work with an ORDER BY.

EDIT : If you are using SQL Server 2005, then have a look into therow_number()function

e.g.

CREATE STOREDPROCEDURE getProductRank
(
@.categoryIDint,
@.productIDint
)AS
CREATE TABLE #t
(
IDint IDENTITY(1,1),
ProductIDint,
CategoryID,
ItemsSold
)
INSERT INTO #t
(SELECT *FROM dbo.Products
WHERE CatehoryID = @.categoryID
ORDER BY ItemsSold)
DECLARE @.rankint
SELECT @.rank = IDFROM #t
WHERE ProductID = @.productID
RETURN @.rank
GO

|||

Hi Jagdipa,

I am using SQL Express 2005 and your reference was very helpful, however it still didn't remove the problem that I didn't want to retrieve all the records in the Category, as I only require 1 of the Products from the table (apologies for being unclear on this in the first post, I think you noticed as you declared a parameter - ProductID = @.ProductID).

I would like to call an aggregate function, something similar to the SUM() function as I mentioned previously.

I'm a little new to SQL so only the basic statements, and don't even know if its possible to achieve what I'm looking for without creating a temporary view or table as you've done?

Thanks.

|||

For those looking for the answer to this question, I have modified this from a custom paging example (which there appears to be lots of the web) to find the rank of an item in a table.

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ItemsSold DESC) AS ProductRankNo, * FROM Products WHERE CategoryID = 10) AS AllProducts

WHERE ProductID = 23

To find the TOP 10 TOPSELLERS, simply change the "WHERE ProductID = 23" to "WHERE ProductRankNo BETWEEN 1 AND 10".

Bear in mind this will only work on SQL Server 2005 databases.

Initially, I was worried that the inner SELECT call would retrieve ALL the records in the table, but this doesn't appear to be so.

'Find & Replace' functionality vanished in Management Studio

Hi,

Whenever I select a sub-menu option from the 'Find & Replace' menu item on the 'Edit' menu I get - nothing. Not the functionality, nor any error box. Was working perfectly earlier.

Any ideas?

Just a thought...

Find & Replace is a dockable tool window. Please scan the windows that you have docked around inside your main SSMS window, maybe it is hidden somewhere there, underneath another window or docked but not slide-out.

Regards,

|||Thanks. That was the exact problem. The dialog box was tucked behind the Taskbar.