Re: How to select all top 1s from different group in a view
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 05 Jan 2006 20:48:30 +0100
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
--
.
- Follow-Ups:
- References:
- Prev by Date: Re: Update 'Date': Set Day(Date) to a Certain Number
- Next by Date: Re: time(urgent)
- Previous by thread: Re: How to select all top 1s from different group in a view
- Next by thread: Re: How to select all top 1s from different group in a view
- Index(es):
Relevant Pages
|
|
