'Select distinct' on records with memo fields, how

hello,
I need to 'select distinct' on a character field
from a table like the following:

select distinct char1, memo1, memo2 from table1
order by char1 into cursor cursor1

The memo fields 'memo1 and memo2' are just remarks
I put in for the field 'char1', what I need to
for 'select distinct' to operate on is only the
field 'char1'

I understand 'distinct' doesn't operate on tables
with memo fields. Now how can I achieve my desired
result? Thank you.

--
jw

GaryBrueggeman
Tue Oct 11 11:23:02 CDT 2005

try
select char1, memo1, memo2 from table1
group by char1 order by char1 into cursor cursor1

"jw" wrote:

> hello,
> I need to 'select distinct' on a character field
> from a table like the following:
>
> select distinct char1, memo1, memo2 from table1
> order by char1 into cursor cursor1
>
> The memo fields 'memo1 and memo2' are just remarks
> I put in for the field 'char1', what I need to
> for 'select distinct' to operate on is only the
> field 'char1'
>
> I understand 'distinct' doesn't operate on tables
> with memo fields. Now how can I achieve my desired
> result? Thank you.
>
> --
> jw
>
>

Cindy
Tue Oct 11 11:59:13 CDT 2005

Hi JW,

When you select distinct Char1 values, what exactly do you expect the memo
values to be? Gary suggested leaving the memo fields out of the group by but
it's invalid SQL.

What you can do is select something like Left(Memo1, 100) and group/distinct
on that.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden


"jw" <jw@nospam.com> wrote in message
news:%23i40a3nzFHA.1040@TK2MSFTNGP14.phx.gbl...
> hello,
> I need to 'select distinct' on a character field
> from a table like the following:
>
> select distinct char1, memo1, memo2 from table1
> order by char1 into cursor cursor1
>
> The memo fields 'memo1 and memo2' are just remarks
> I put in for the field 'char1', what I need to
> for 'select distinct' to operate on is only the
> field 'char1'
>
> I understand 'distinct' doesn't operate on tables
> with memo fields. Now how can I achieve my desired
> result? Thank you.

Olaf
Tue Oct 11 12:01:34 CDT 2005

If the number of records isn't very high you may do:

select char1, memo1, memo2 from tabelle1 where ... into cursor curUnique Readwrite
* this means not restricting to only the top 1 record for each char1

* now delete all unwanted records with the help of an index of type "unique":

select curUnique
index on char1 tag xchar1 unique
set order to
delete all
set order to xchar1
recall all

Bye, Olaf.

jw
Tue Oct 11 12:52:22 CDT 2005

LEFT(memo1,100) does the job.
Thank you all for the help. .

--
jw


jw wrote:
> hello,
> I need to 'select distinct' on a character field
> from a table like the following:
>
> select distinct char1, memo1, memo2 from table1
> order by char1 into cursor cursor1
>
> The memo fields 'memo1 and memo2' are just remarks
> I put in for the field 'char1', what I need to
> for 'select distinct' to operate on is only the
> field 'char1'
>
> I understand 'distinct' doesn't operate on tables
> with memo fields. Now how can I achieve my desired
> result? Thank you.
>
> --
> jw
>

Olaf
Thu Oct 13 04:08:10 CDT 2005

> LEFT(memo1,100) does the job.
> Thank you all for the help. .
The maximum you can go up to is 254 chars.
If you go higher VFP will cut it down to that length:

create cursor curMemo (mMemo M)
insert into curMemo values ("hello, world")
select Left(mMemo,1000) as leftMemo from curMemo into cursor curResult
? len(curresult.leftMemo)

If you only have short texts this may be okay for you.

Bye, Olaf.

Andrew
Fri Oct 14 02:32:41 CDT 2005

"jw" <jw@nospam.com> wrote in message
news:%23i40a3nzFHA.1040@TK2MSFTNGP14.phx.gbl...
> hello,
> I need to 'select distinct' on a character field
> from a table like the following:
>
> select distinct char1, memo1, memo2 from table1
> order by char1 into cursor cursor1
>
> The memo fields 'memo1 and memo2' are just remarks
> I put in for the field 'char1', what I need to
> for 'select distinct' to operate on is only the
> field 'char1'
>
> I understand 'distinct' doesn't operate on tables
> with memo fields. Now how can I achieve my desired
> result? Thank you.

This doesn't appear to be length limited at 254 chars:

SELECT DISTINCT char1, memo1, memo2, ;
SYS(2007, memo1) AS chksum1, ;
SYS(2007, memo2) AS chksum2 ;
FROM table1 ;
ORDER BY char1 ;
INTO CURSOR cursor1

I don't know how good SYS(2007) is with regard to collisions but it does say
in the help that it is to be used for comparing character expressions.

--
HTH
Andrew Howell

Olaf
Fri Oct 14 06:18:45 CDT 2005

> This doesn't appear to be length limited at 254 chars:
>
> SELECT DISTINCT char1, memo1, memo2, ;
> SYS(2007, memo1) AS chksum1, ;
> SYS(2007, memo2) AS chksum2 ;
> FROM table1 ;
> ORDER BY char1 ;
> INTO CURSOR cursor1
>
> I don't know how good SYS(2007) is with regard to collisions but it does say in the help that it is to be used for comparing
> character expressions.
This is still invalid in VFP8 or 9, as a DISTINCT isn't allowed for
memo, blob or general fields. You must leave the memos out:

SELECT DISTINCT char1, ;
SYS(2007, memo1) AS chksum1, ;
SYS(2007, memo2) AS chksum2 ;
FROM table1 ;
INTO CURSOR cursor1

But SYS(2007) results in a value presumably between 0 and 2^16,
so you have only 64K different values. The chance is low but with
thousands of records you may miss some records which have identical
checksums but different texts in the memo. You may select/compare both
Left(memo,254) and chksum to get better results than with each single
term.

Bye, Olaf.