Re: SQL 'distinct' keyword

[Bill Robins <brobins@xxxxxxxxxxxxx>]

Mary,

I had never really considered what the distinct option did, but I guess you 
could
consider it a group by select without any summary options.  So, if you look at 
it in
that context I think you are going to have problems.  I am not quite sure what 
it it
you are trying to accomplish, but let me take a stab at it, it sounds like you 
want
to list all of the original relevant data but list the highest value of the date
value.  To do what I think you want to do, you will either need to create a
summarized copy of your file, or a view.

Create View mylib/filex as Select keyflda, keyfldb, max(datefld) as maxdate From
filea Group by keyflda, keyfldb

Select fa.keyflda, fa.keyfldb, dataflda, datafldb, maxdate From filea fa join 
filex
fx on fa.keyflda=fx.keyflda and fa.keyfldb=fx.keyfldb

If this isn't what you want, we can try again.

Bill


> I have a file with duplicate records and I want the most recent record to
> float(ordering it on a descending date) to the top BUT I also want to select
> additional fields  that I don't want SQL to consider when it does the
> 'SELECT DISTINCT'. Is this possible?
>


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---