Re: SQL - Select lowest

[Michael_Schutte@xxxxxxxxxxxx]

Ok,

maybe you only need to create this as the cte.

so do this first to see if this gets your results.

with cte as (
  select field1, min(field2) as minfield2
   from michael/myduptable
   group by field1)
Select a.*
  from someothertable a
  join cte b on a.field1 = b.field1 and a.field2 = b.minfield2






--

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 10:08:35 AM:

> I think my problem is I'm joining several tables together, so the min
> doesn't work since it's not a 'result' yet. I'm trying to min on one
field
> that's derived from a join. I think that's why I need a CTE - to join all
> the data together (dups and all), then select the min of that. But to do
> that, I would have to specify all the columns from the CTE, right? IOW, I
> couldn't specify *, even though the fields in the select match up to the
> fields in the resultant file. And, if I have to specify the fields, then
I
> can't use literals for a couple of the fields (as I'm doing) because It's
> not producing a field name. I'm doing this in embedded SQL.
>
> On Tue, Dec 8, 2009 at 9:57 AM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:
> > My only thought with the CTE is the select to build everything is big.
I'm
> > also trying to do an insert of this combined date into another table.
> >
> >
> > On Tue, Dec 8, 2009 at 9:43 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:
> >
> > > 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.
> > >
> > > --
> > > 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.
> --
> 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.