Re: SQL - Select lowest

[Michael_Schutte@xxxxxxxxxxxx]

There's no need to use the CTE, you're not doing anything special with it.

You can just simply do

select field1, min(field2) from michael/myduptable group by field1


--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com



midrange-l-bounces@xxxxxxxxxxxx wrote on 12/08/2009 09:36:43 AM:

> Oh...this works...I think...
>
> with temp as (select * from michael/myduptable) select field1,
> min(field2) from temp group by field1
>
> Does this make sense?
>
>
> On Tue, Dec 8, 2009 at 9:32 AM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:
> > Things like this confuse me until someone shows me how simple it is. :)
> > This is what I want to do:
> >
> > with temp as (select * from michael/myduptable) select * from temp
having
> > min(field2)
> >
> > Here's the data:
> >
> > FIELD1   FIELD2
> >      1   ONE
> >      2   TWOA
> >      2   TWOB
> >      3   THREE
> >      4   FOURA
> >      4   FOURB
> >      4   FOURC
> >
> > I want this:
> > 1 ONE
> > 2 TWOA
> > 3 THREE
> > 4 FOURA
> >
> > I'm using a CTE table because the select that makes myduptable (in real
> > life) is complex, and I thought this would make it easier. Best holiday
> > wishes to anyone that can give me the answer. :)
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.