Re: How to select all top 1s from different group in a view

Hi Hugo,

Thanks for your reply.
The logic is:
A. Select highest non-NULL END_DATE against a CONTRACT_KEY, say 1, you got:
CONTRACT_KEY END_DATE STEP
1 3/7/2006 3

B. Select highest non-NULL END_DATE against a CONTRACT_KEY, say 2, use
highest STEP as tiebreaker if necessary. you got:
CONTRACT_KEY END_DATE STEP
2 3/1/2005 2

C. If all END_DATEs are NULL for a CONTRACT_KEY, say 3, selecting ANY one
row within that CONTRACT_KEY will be sufficient. Here I happen to pick first
row, so you got:
CONTRACT_KEY END_DATE STEP
3 <NULL> 1

Notes:
CONTRACT_KEY field is non-null;
END_DATE field is nullable;
STEP field is non-null and has same number of steps 1 to 3 for each
CONTRACT_KEY.

The example that I gave earlier is a view, I am not sure if you put it in a
single table as is, will you be able to write a query against that table?

Please kindly let me know if you need any other information.

Thanks so much again

-adam


"Hugo Kornelis" wrote:

> On Thu, 5 Jan 2006 10:48:07 -0800, adam wrote:
>
> >Hi Steve,
> >
> >Thank for your reply.
> >Sorry, I should have layout my view in more clear way. Here is the view:
> >CONTRACT_KEY END_DATE STEP
> >1 <NULL> 1
> >1 2/5/2006 2
> >1 3/7/2006 3
> >2 3/1/2005 1
> >2 3/1/2005 2
> >2 <NULL> 3
> >3 <NULL> 1
> >3 <NULL> 2
> >3 <NULL> 3
> >
> >How could I wrote a query to generate the following result:
> >CONTRACT_KEY END_DATE STEP
> >1 3/7/2006 3
> >2 3/1/2005 2
> >3 <NULL> 1
> >
> >Thanks so much again!
>
> Hi adams,
>
> What's the logic of this result? Is it "highest non-NULL END_DATE, use
> highest STEP as tiebreaker, but lowest STEP if all END_DATE are NULL"?
> Or is it "Highest STEP with non-NULL END_DATE or lowest STEP if all
> END_DATE are NULL"? Or maybe something else?
>
> It would also help if you'd post a CREATE TABLE statement, including all
> constraints, properties, and indexes. The sample data alone is
> insufficient to determine which columns or combinations of columns are
> unique and which columns are nullable.
>
> Oh, and apart from the CREATE TABLE statement, some INSERT statements
> with sample data would be great too. If only to enable us to copy and
> paste them to create a test table. See www.aspfaq.com/5006.
>
> Best, Hugo
>
> --
> Angband spoilers: http://ourworld.compuserve.com/homepages/hugo_kornelis/Angband/Spoiler/index.htm
> Angband UI Patch: http://ourworld.compuserve.com/homepages/hugo_kornelis/Angband/UIpatch/index.htm
> --
>
.