Re: SQL - Select lowest

I think that's the missing link Richard. I will do some testing and see if I
can get this to work. Alas, because of time considerations, I wrote the half
a dozen lines of RPG to fix it. I want to try the 'pure' SQL way though, so
I'll give this a go. Thanks!

On Tue, Dec 8, 2009 at 11:09 AM, Richard Casey <casey_r@xxxxxxxxxxxxxxxx>wrote:

Michael,

If I understand correctly, your "myduptable" is actually the result set of
a
much larger select statement including joins as well as some literal
fields.
You're looking for the row in that result set (including all fields) that
has the minimum value for particular field. There is some key field (or
fields) that would uniquely identify a row in your result set.

I think using multiple CTEs would give you what you want. See how this
works.

with myresult as (select * from michael/myduptable),
mymin as (select field1 key1, min(field2) key2 from myresult group by
field1)
select myresult.* from myresult
inner join mymin on field1 = key1 and field2 = key2

The "select * from michael/myduptable" in the first CTE would be replaced
with your big select statement. The second CTE would produce a list of
records containing the key fields for the record in CTE #1 with the minimum
value in the desired field. The inner join between CTE #1 and CTE #2 should
give you all the fields in your big select statement.


FYI, you can assign a field name to a literal. For example:

with mytest as (select field1, field2, 'xyz' field3 from
michael/myduptable)
select field1, field2, field3 from mytest


Have fun!

Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, December 08, 2009 10:09 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL - Select lowest

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.

--
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.