Top 12 values?

[S Davis]

Hello,

How would I go about creating a Top 12 values query?

My data is as follows:

2101...1/1/2003...CVIP
2101...2/2/2003...RR
2101...3/3/2003...CVIP
2102...1/1/2003...EE
2102...2/2/2003...RR
2102...3/3/2003...CVIP

What I would like to do is return the top 12 dates according to the
first column - so the top 12 dates of 2101, the top 12 dates of 2102,
etc, REGARDLESS of column 3, so that I end up with the top 12 CVIP, RR,
EE, etc per 2101, 2102, etc.

Thanks
-S

 

[Ken Sheridan]

I assume by top 12 you mean in date descending order, i.e. the 12 most recent
dates. Use a subquery to determine the Top 12 dates for each value of the
first column and restrict the outer query's result set to those rows in each
set of rows returned by the subquery:

SELECT *
FROM YourTable As T1
WHERE YourDate IN
(SELECT TOP 12 YourDate
FROM YourTable AS T2
WHERE T2.YourFirstColumn = T1.YourFirstColumn
ORDER BY YourDate DESC);

If you want the top 12 rows in date ascending order simply delete the DESC
from the query.

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

You said top 12 but did not say in what order. This is in descinding order.
To change to pull the oldest dates the edit >= T.Column2 to <=
T.Column2 and remove DESC in the last line.
SELECT T.Column1, T.Column2, T.Column3, (SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2) AS Rank
FROM S_Davis AS T
WHERE ((((SELECT COUNT(*)
FROM [S_Davis] T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 >= T.Column2))<=12))
ORDER BY T.Column1, T.Column2 DESC;

 

[S Davis]

Thanks so much - that works perfectly and is really useful!
-S

 

[John Spencer]

Try something like the following where you fill in your table and field
names.

SELECT SomeNumber
, DateField,
CVIPRR
FROM SomeTable
WHERE DateField in (SELECT TOP 12 DateField
FROM SomeTable as Tmp
WHERE TmpSomeNumber = SomeTable.SomeNumber
and Tmp.CVIPRR = SomeTable.CVIPRR
ORDER BY DateField)

 

[S Davis]

Thanks for all the replies everyone. There is a bit of inconsistency
with the results however - on some 2101, 2102's etc, 13 results are
returned instead of 12. Is there any reason why this is happening?
There are numerous instances where similar/exact dates occur, but the
occurrence of >12 returns seems to be random. Any insight?

 

[KARL DEWEY]

I think that you will find that the 12th and 13th dates are the same.

Do you have additional criteria if the 12th and 13th dates are the same?

I think you will find that my SQL will pull only 12. You will not know that
the 12th and 13th dates would be the same.

 

[John Spencer]

I would guess that is because you have ties - that is the dateField appears more
than one time for a combination of SomeNumber and CVIPRR fields.

If you have a primary key field this query or Karl Dewey's can be rewritten to
use that value to ensure you are returning only 12 records per combination.

 

[S Davis]

Thanjs for the replies. This has helped take me a step in the right
direction.