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
.

Relevant Pages

  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)
  • Re: TRICKY SQL
    ... Thanks Hugo and everyone who helped me in solving this issue. ... > Hi Joe, ... > If you had this table, solving your query would be simple. ...
    (microsoft.public.sqlserver.server)
  • Re: TRICKY SQL
    ... Thanks Hugo and everyone who helped me in solving this issue. ... > Hi Joe, ... > If you had this table, solving your query would be simple. ...
    (microsoft.public.sqlserver.programming)
  • Re: sql server issue.
    ... Hugo I found one thing... ... There are three reasons for the performance problem i think... ... When i execute with the sample script which i have send it is getting in ... I am attaching the query i had written. ...
    (microsoft.public.sqlserver.programming)
  • Re: Index View
    ... Start with the execution plan. ... Prabhat wrote: ... >> you can't use this for your query. ... >> Best, Hugo ...
    (microsoft.public.sqlserver.programming)