(SQL) Select Statement Woes on "Group" option

[rpp]

Hi Experts

I am trying to create a SQL statement using the group option but find it behaves similar to that of the "Distinct" option. It is eliminating the matching or grouped records.

The Deal:
fields: phone, name

I want to be able to sort on name but group by phone
SELECT ALL * FROM testsort GROUP BY phone ORDER BY name

I have 1079 records to start, after the SQL runs I have 854 (Where the rest go?)

My hope was to have an alphabetical listing of names except where the different names have the same phone number then I would have hoped they would have been "GROUPED" together by the phone number.

Is this doable with the SQL or do I need to create a separate sort field and force common data into it before sorting.

Thanks in advance,
Rich

 

[ramani]

HI

SELECT ALL * FROM testsort ORDER BY name,phone




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[rpp]

Hi Ramani,

I wish it where that easy.

My data has duplicate records, names listed as first/last and last/first in the name field, my hope to group on phone but still have an alphabetical list of names, ie., I would not even care where this guy ended up sorting, either in the "F's" or the "P's" as long as they were together by the common phone, but the list needs to be alphabetical.

ie.
Foxpro....123-1231
Profox....123-1231

Thanks for the quick reply,

Rich

 

[ramani]

Hi

SELECT ALL *, name AS tname FROM testsort ORDER BY phone ;
INTO CURSOR myCursor READWRITE

(If you are using older versions of VFP.. make it to a DBF instead of CURSOR so that READWRITE can be removed and a writable cursor is created)

LOCATE
SCATTER MEMVAR BLANK
SCAN
m.Name
IF m.phone = phone
REPLACE tname WITH m.tname
ENDIF
SCATTER MEMVAR
ENDSCAN

SELECT * FROM myCursor ORDER BY tName, name




ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/