|
|
|
date: Wed, 4 Jan 2006 14:23:05 -0800,
group: microsoft.public.sqlserver.mseq
back
Re: How to select all top 1s from different group in a view
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
--
date: Thu, 05 Jan 2006 20:48:30 +0100
author: Hugo Kornelis
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
> --
>
date: Thu, 5 Jan 2006 14:06:02 -0800
author: adam
Re: How to select all top 1s from different group in a view
On Thu, 5 Jan 2006 14:06:02 -0800, adam wrote:
>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
Hi adam,
Since the tie-breaker for a "NULLs-only" situation is irrelevant, I'll
take the easy way out and use highest STEP as tie-breaker in ALL
situations.
Try if the following variation on Steve's code works for you:
SELECT Contract_Key, Step, End_Date
FROM YourTable AS T1
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.Contract_Key = T1.Contract_Key
AND ( COALESCE(T2.End_Date, '19000101') > COALESCE(T1.End_Date,
'19000101')
OR (COALESCE(T2.End_Date, '19000101') = COALESCE(T1.End_Date,
'19000101')
AND T2.Step > T1.Step)))
Or, if (Contract_Key, Step) is a PRIMARY KEY or UNIQUE, use this query:
SELECT Contract_Key, Step, End_Date
FROM YourTable AS T1
WHERE Step =
(SELECT TOP 1 Step
FROM YourTable AS T2
WHERE T2.Contract_Key = T1.Contract_Key
ORDER BY End_Date DESC, Step DESC)
--
Hugo Kornelis, SQL Server MVP
date: Sat, 07 Jan 2006 22:23:45 +0100
author: Hugo Kornelis
|
|
