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

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!

-adams


"Steve Kass" wrote:

> A pattern for this, assuming no NULLs in your columns, is
>
> select
> Contract_KEY, Step
> from yourTable as T1
> where not exists (
> select * from yourTable as T2
> where T2.Contract_KEY = T1.Contract_KEY
> and T2.Step < T1.Step
> )
>
> Steve Kass
> Drew University
>
> adam wrote:
>
> >Hi SQL Query Guru,
> >
> >I have a view like this:
> >
> >Contract_KEY Step
> >1 1
> >1 3
> >2 1
> >2 5
> >2 4
> >
> >How do I write a query to retrieve the following result:
> >1 3
> >2 5
> >
> >Thanks very much!
> >
> >-adams
> >
> >
>
.

Relevant Pages

  • Re: Excel -> SQL Server
    ... Steve Kass wrote: ... > If I create D:\format.xls with a sheet named actions, ... I cut and pasted that statement directly from Query Analyser. ... just after I've run the command in QA and I try to open the xl ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Table and Excel - Maybe not the best way??
    ... There's no guarantee that a query of the form ... you need to cut and paste real queries that run without syntax ... >> syntax errors give results. ... >> Steve Kass ...
    (microsoft.public.sqlserver.programming)
  • Generated column YES or NO if a value exists in another table...
    ... The above query returns all the rows that are active in the Choices table. ... The layout for the second table, named Choices2Tickets, is: ... Choices2Ticket table where the TicketKey is 123 for the current ChoiceKey. ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: TRICKY SQL
    ... for this query, and you can write ... each id, and itf for example ... Steve Kass ... Drew University ...
    (microsoft.public.sqlserver.server)
  • Re: TRICKY SQL
    ... for this query, and you can write ... each id, and itf for example ... Steve Kass ... Drew University ...
    (microsoft.public.sqlserver.programming)