Need display only one record of multiple records with the same value from particular field

[titoneon]

Hi,
I have a table named "Eng_jobs.dbf" in there i have these records, only need to display the "job_no field value" only one record from the multiple values.

Fields Job_no c(6)   Part_no c(12)     Part_desc c(28)
values 11735         114376-1311       INSULATOR - DIE HEAD BRACKET
       11746         117462-515        abcdefg
       103614        100283-1475       CLAMP COLLAR
       103614        100283-472        BRACKET  
       103614        111525-258        GAUGE BLOCK  
       109942        100802-110        IDLER BRACKET-ADJ  
       109942        100802-1112       SPROCKET

so actually what i need to obtain is
11735
11746
103614
109942
i have been using "select job_no from eng_jobs distinct into cursor result order by 1"
but still don't get as i want to accomplish above
Can anyone suggest me please ?
Thanks

 

[Olaf Doschke]

It's

select distinct job_no from eng_jobs into cursor result order by 1

http://msdn.microsoft.com/en-us/library/h09t6a82(v=vs.80).aspx

If you can't understand syntax descriptions it's time to learn how to read this. Two simple things first: square brackets enclose optional things and [,...] means the previous part can be repeated several times.
SELECT-SQL is quite a complex command, but DISTINC isn't put to the FROM clause. Where did you get that from?

Bye, Olaf.

 

[titoneon]

Olaf,
Thanks for correcting me, actually this is what i was doing before

"SELECT * distinct from eng_jobs INTO CURSOR result ORDER BY 1"

But i was not getting what i wanted, i know realized, that i did not specified the field which, the Distinct clause should be affecting
so for an stupid reason i typed incorrectly when i posted here, anyway i was wrong cause i did not specified like this

"select distinct job_no from eng_jobs into cursor result order by 1 "

no wonder why i could not get the result i wanted by doing this (SELECT * distinct from eng_jobs INTO CURSOR result ORDER BY 1)

Thanks for the correction, yes i can understand Sintax but no one is perfect.

 

[EzLogic]

What Olaf showed you gives you exactly what you need. Perfect solution.

To elaborate a bit more, in case your client wants more or you want to show more,
example, you want to show Job# and the # of parts scanned etc.. or something like that
you can do a group by.

so, you would do this:

Select Job_No from Eng_jobs Group by Job_No Order by Job_No into cursor Result && Will give same effect as Distinct.
this can be further helpful like this:

Select Job_No, count(*) as JobCount from Eng_Jobs order by Job_No iinto cursor Result && will give you same as distinct, but with count..

anyway... just to show you some variations...

Ez Logic
Michigan

 

[EzLogic]

Correction.. last SQL Select should be:
select Job_No,count(*) from Eng_Jobs Group by Job_No Order by Job_No into cursor Results

Ez Logic
Michigan

 

[Olaf Doschke]

Yes, also * is wrong in that one, you use * for all fields. If you don't want all fields, don't Select *.

Syntactically correct would also be SELECT DISTINCT * From ..., but consider what this means: DISTINCT then is applied to all fields, which means not every single field, but the whole record. As there is other data in other fields, you'd get all records anyway. DISTINCT * would only suppress whole identical records, with same job_no, part_no, and part_desc.

Bye, Olaf.

 

[titoneon]

Olaf,
Yes that is exactly what i did wrong "SELECT DISTINCT * From ...," that is why my results were not as i expected, so my bad anyway for not realizing that i should include the field Job_no as you posted, anyway i will thank you for your corrections and forgive me for my ignorance.

 

[Olaf Doschke]

It's obviously not your day. Doesn't matter. I'm always also writing for anyone finding a thread having a similar question.

Ez Logic also is right. Some SQL purists even will tell you to always use group by and never distinct. In the long term a distinct may fail, once you add further fields, if you do so in a query with a group by you get an error, if not also adding that field to the GROUP BY clause or aggregate with MIN, MAX, COUNT, SUM, AVG or other aggregation functions.

And indeed it's quite complex to do what you put into the thread caption, to select one of the records as full record, but only one of them for each job_no.

The easy way is to make use of VFPs bad implementation of group by before VFP8:

SET ENGINEBEHAVIOR 70
SELECT * from eng_jobs GROUP BY job_no INTO CURSOR result

* result would be
Job_no c(6)   Part_no c(12)     Part_desc c(28)
11735         114376-1311       INSULATOR - DIE HEAD BRACKET
11746         117462-515        abcdefg
103614        100283-1475       CLAMP COLLAR
109942        100802-110        IDLER BRACKET-ADJ

With the correct VFP8/9 enginebehaviour you could do it that way:

SET ENGINEBEHAVIOR 90
SELECT eng_jobs.* from eng_jobs INNER JOIN
(Select job_no, min(Part_no) as part_no GROUP BY job_no) tmp On tmp.job_no = eng_jobs.job_no AND tmp.part_no = eng_jobs.part_no
INTO CURSOR result

* result would also be
Job_no c(6)   Part_no c(12)     Part_desc c(28)
11735         114376-1311       INSULATOR - DIE HEAD BRACKET
11746         117462-515        abcdefg
103614        100283-1475       CLAMP COLLAR
109942        100802-110        IDLER BRACKET-ADJ

And there would be one simpler way to do it with a unique index

USE eng_jobs
INDEX ON job_no Tag firstpart UNIQUE
BROWSE

The records with a repeated job_no are still in the DBF, but not in the index, so the index filters to all first occurrances of a job_no.

There always are many ways to solve any problem.

Bye, Olaf.

 

[titoneon]

Olaf,
I am just testing it and i am getting "Syntax error" om

SET ENGINEBEHAVIOR 90
SELECT eng_jobs.* from eng_jobs INNER JOIN
(Select job_no, min(Part_no) as part_no GROUP BY job_no) tmp On tmp.job_no = eng_jobs.job_no AND tmp.part_no = eng_jobs.part_no
INTO CURSOR result
can you tell here what could be ?
Thanks

 

[Olaf Doschke]

Line continuation is missing, so put semicolons at the end of line 2 and 3.

Bye, Olaf.