Help with a complicated query

SQL Apprentice Question
have a data set like the one below:

Qualifier Date Whole/Partial Partial Count
A 1/12/2000 W NULL
A 1/3/2001 P 1
A 1/6/2001 P 2
A 1/9/2001 P 3
A 1/12/2001 P 4
B 1/4/2001 W NULL
B 1/7/2001 P 1
B 1/10/2001 P 2
B 1/1/2002 P 3
B 1/1/2006 P 4
B 1/4/2002 P 4


There are more than a million rows of data in the database


What would be the most efficient SQL server query to extract for each
qualifier the
the latest 4 Partial dates given a reference date (like say getDate() or a
date)



Celko Answers
>> I have a data set [table?] like the one below: <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Why do you have a running count in a table? Also, learn the basics of
the trade like ISO-8601 data formats. Did you mean something more like
this, if you knew DDL?


CREATE TABLE Foobar
(foobar_qualifier CHAR(1) N0T NULL,
posting_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
wp_flag CHAR(1) DEFAULT 'W' NOT NULL
CHECK (foobar_qualifier IN ('W', 'P')),
PRIMARY KEY (foobar_qualifier, posting_date)); -- wild guess?



>> What would be the most efficient SQL server query to extract for each qualifier the the latest 4 Partial dates given a reference date (like say CURRENT_TIMESTAMP or a


date) <<

SELECT foobar_qualifier, posting_date,
ROW_NUMBER() OVER (PARTITION BY foobar_qualifier ORDER BY
posting_date)
AS rn
FROM Foobar
WHERE rn <= 4
AND wp_flag = 'P'
AND posting_date >= @my_date;


Untested, SQL 2005.