Re: SQL Question

[Jose Perez <joseenocperez.jp@xxxxxxxxx>]

You can use distinct with a subselect for example
Select distinct Item from (select Ordno, item from ordertable where
order=:order# order by fieldname)

On Mon, Jan 13, 2020, 1:33 PM Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
wrote:

> Paul & Steve,
>
> Thank you!  That's the ticket.  I don't know why I can't get my RPG brain
> to think like that.
>
> Greg
>
>
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
> Paul Therrien
> Sent: Monday, January 13, 2020 12:46 PM
> To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: SQL Question
>
> Oops. Don't group by max(TimeField)
>
> Select Company, Orderfrom , max(timefield) from Myfile Where userID=:value
> Group by Company, Orderfrom  Order by max(TimeField);
>
> Paul
>
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Paul
> Therrien
> Sent: Monday, January 13, 2020 12:45 PM
> To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: SQL Question
>
> Here is an alternative using group by
>
> Select Company, Orderfrom , max(timefield) from Myfile Where userID=:value
> Group by Company, Orderfrom , max(timefield) Order by max(TimeField);
>
>
>
> Paul Therrien
> Andeco Software, LLC
> paultherrien@xxxxxxxxxxxxxxxxxx
> 225-229-2491
>
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> Wilburn
> Sent: Monday, January 13, 2020 12:15 PM
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: SQL Question
>
> Using embedded SQL... I have a "Select Distinct" statement that is
> returning
> the result I need... However, I need the result set sorted by a field/row
> that is NOT in the distinct clause.  Is there a way to do this?
>
> Ex:
> Select distinct Company, Order from Myfile Where userID=:value Order by
> TimeField;
>
> This produces an SQL0214 error...
>
> Is there another way I'm not thinking about?
>
> Thx
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com