Prev: Will this newsgroup be cancelled ?
Next: String quotes and variales
From: Jon Spivey on 21 Jul 2010 10:53
I've got a table like this

ModelID - int, primary key
MakeID - int, foreign key to makes table
SalesRank - int higher = better.
..... other fields

I want the 4 best selling models (highest sales rank) for each of the 6 best
selling makes. The 6 best selling makes rarely change so I could get away
with hard coding them rather than calculating each time. Obviously 6 queries
would do it
select top 4 * from models where makeid =1 order by salesrank desc
select top 4 * from models where makeid =2 order by salesrank desc
etc

Not too bad efficiency wise as the salesrank only updates once a day so I
can cache the page until the underlying data changes. But is it possible to
pull the 24 models grouped by make with 1 query?

Thanks,
Jon


From: Plamen Ratchev on 21 Jul 2010 11:36
Here is one solution for SQL Server 2005/2008. It uses common table
expression (like derived table) and the ROW_NUMBER ranking function to
define the ranks.

If you want to hardcode the top selling makes:

WITH Ranked AS (
SELECT makeid, modelid, salesrank,
ROW_NUMBER() OVER(PARTITION BY makeid ORDER BY salesrank DESC)
AS rk
FROM Models
WHERE makeid IN (1, 2, 3, 4, 5, 6))
SELECT makeid, modelid, salesrank
FROM Ranked
WHERE rk <= 4;

To calculate the top 6 selling makes and then the top 4 models for
each make:

WITH Totals AS (
SELECT makeid, modelid, salesrank,
ROW_NUMBER() OVER(PARTITION BY makeid ORDER BY salesrank DESC)
AS rk,
SUM(salesrank) OVER(PARTITION BY makeid) AS make_total
FROM Models),
Ranked AS (
SELECT makeid, modelid, salesrank, rk,
DENSE_RANK() OVER(ORDER BY make_total DESC) AS make_rk
FROM Totals)
SELECT makeid, modelid, salesrank
FROM Ranked
WHERE rk <= 4
AND make_rk <= 6;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Tom Cooper on 21 Jul 2010 11:43
With ModelsRanked As
(Select ModelID, MakeID, SalesRank,
Row_Number() Over(Partition By MakeID Order By SalesRank Desc) As rn
From models
Where MakeID In (1,2, ...))
Select ModelID, MakeID, SalesRank
From ModelsRanked
Where rn <= 4;

That's if you are hardcoding the MakeID's you are looking at. If you don't
hard code them, then change MakeID In (1, 2, ...) to MakeID In (Select <some
select statement that selects the six MakeID's you want>).

Tom

"Jon Spivey" <js(a)nisusnewmedia.com> wrote in message
news:up%23LISOKLHA.4824(a)TK2MSFTNGP05.phx.gbl...
> I've got a table like this
>
> ModelID - int, primary key
> MakeID - int, foreign key to makes table
> SalesRank - int higher = better.
> .... other fields
>
> I want the 4 best selling models (highest sales rank) for each of the 6
> best
> selling makes. The 6 best selling makes rarely change so I could get away
> with hard coding them rather than calculating each time. Obviously 6
> queries
> would do it
> select top 4 * from models where makeid =1 order by salesrank desc
> select top 4 * from models where makeid =2 order by salesrank desc
> etc
>
> Not too bad efficiency wise as the salesrank only updates once a day so I
> can cache the page until the underlying data changes. But is it possible
> to
> pull the 24 models grouped by make with 1 query?
>
> Thanks,
> Jon
>
>

From: --CELKO-- on 21 Jul 2010 12:03
I am confused by your narrative, because Ia m thinking about
automobiles and other manufactured goods. The make is the higher
level in the hierarchy and the key is (make, model) -- i.e. (make,
model) => "Honda Civic", "Ford Fairlane", etc. I don't have my old
NCIC book in front of me, but make and model are alpha codes, not
integers

You also gave us no rule for determining the top 6 makes.

You will need to use a DENSE_RANK() function in case of ties, but that
is about as far as we can get without a spec.
From: Jon Spivey on 21 Jul 2010 12:46
Perfect. Thanks Tom

Cheers,
Jon

"Tom Cooper" <tomcooper(a)comcast.net> wrote in message
news:%23w8$ttOKLHA.4936(a)TK2MSFTNGP05.phx.gbl...
> With ModelsRanked As
> (Select ModelID, MakeID, SalesRank,
> Row_Number() Over(Partition By MakeID Order By SalesRank Desc) As rn
> From models
> Where MakeID In (1,2, ...))
> Select ModelID, MakeID, SalesRank
> From ModelsRanked
> Where rn <= 4;
>
> That's if you are hardcoding the MakeID's you are looking at. If you
> don't hard code them, then change MakeID In (1, 2, ...) to MakeID In
> (Select <some select statement that selects the six MakeID's you want>).
>
> Tom
>
> "Jon Spivey" <js(a)nisusnewmedia.com> wrote in message
> news:up%23LISOKLHA.4824(a)TK2MSFTNGP05.phx.gbl...
>> I've got a table like this
>>
>> ModelID - int, primary key
>> MakeID - int, foreign key to makes table
>> SalesRank - int higher = better.
>> .... other fields
>>
>> I want the 4 best selling models (highest sales rank) for each of the 6
>> best
>> selling makes. The 6 best selling makes rarely change so I could get away
>> with hard coding them rather than calculating each time. Obviously 6
>> queries
>> would do it
>> select top 4 * from models where makeid =1 order by salesrank desc
>> select top 4 * from models where makeid =2 order by salesrank desc
>> etc
>>
>> Not too bad efficiency wise as the salesrank only updates once a day so I
>> can cache the page until the underlying data changes. But is it possible
>> to
>> pull the 24 models grouped by make with 1 query?
>>
>> Thanks,
>> Jon
>>
>>
>


 | 
Pages: 1
Prev: Will this newsgroup be cancelled ?
Next: String quotes and variales