SQL query help pls.

vickr1z · 10-06-2004, 09:40 PM

good day to all.
i have this sql query and return a result upto 10,000 records.

Code:

SELECT cocv.personality_id, province_code, municipality_code, votes, personalities.personality_nam_last, personalities.personality_nam_first,
   certificates_candidacy.position_id, position_prov_ipd_id, position_munc_ipd_id, leg_dist_ipd_id
FROM (cocv INNER JOIN personalities
   ON cocv.personality_id = personalities.personality_id) INNER JOIN certificates_candidacy
   ON cocv.personality_id = certificates_candidacy.personality_id
WHERE certificates_candidacy.position_id = '30';

but when i add a GROUP BY function

it gives me different numbers of results. how is that happened?

Tinkster · 10-06-2004, 11:56 PM

Where are you adding the grouping?

Cheers,
Tink

vickr1z · 10-07-2004, 05:58 AM

Quote:

Originally posted by Tinkster
Where are you adding the grouping?

Cheers,
Tink

i add the grouping at the last of the sql

Code:

SELECT cocv.personality_id, province_code, municipality_code, votes, personalities.personality_nam_last, personalities.personality_nam_first,
   certificates_candidacy.position_id, position_prov_ipd_id, position_munc_ipd_id, leg_dist_ipd_id
FROM (cocv INNER JOIN personalities
   ON cocv.personality_id = personalities.personality_id) INNER JOIN certificates_candidacy
   ON cocv.personality_id = certificates_candidacy.personality_id
WHERE certificates_candidacy.position_id = '30'
GROUP BY certificates_candidacy.leg_dist_ipd_id, cocv.personality_id, province_code, municipality_code, votes, personalities.personality_nam_last, personalities.personality_nam_first,
   certificates_candidacy.position_id, position_prov_ipd_id, position_munc_ipd_id;

Tinkster · 10-08-2004, 04:44 PM

I don't understand what your problem is - maybe you
mistook "group by" for "order by"? Group by, by definition,
will collapse multiple entries for one of the criteria into
a single hit ... the more group-by you do, the less results
you will get.

Cheers,
Tink

Thomasseafort · 10-14-2004, 12:13 PM

That is one long join statement do you really want all of that in a table whew.

Try at the end ORDER BY certificates_candidacy.leg_dist_ipd_id. just one category
Or order by just one category
What is happening is that it will order by the first one, then the second, then the third
and so on so you are really getting the same date multiple times just in different groups and orders

vickr1z · 10-18-2004, 05:20 AM

Quote:

Originally posted by Tinkster
I don't understand what your problem is - maybe you
mistook "group by" for "order by"? Group by, by definition,
will collapse multiple entries for one of the criteria into
a single hit ... the more group-by you do, the less results
you will get.

Cheers,
Tink

Thanks Tinkster..
You really give em a shagg!! i mistook those group by the order by..

More power!!

Thomasseafort · 10-18-2004, 08:23 AM

It is more proper procedure to group by only one category if you want to limit the
hit result you should do this in your select statement.

vickr1z · 10-18-2004, 08:51 PM

Quote:

Originally posted by Thomasseafort
It is more proper procedure to group by only one category if you want to limit the
hit result you should do this in your select statement.

Thanks for all of your help folks.
In my previous post i just want to use order by on my SELECT query rather than group by..

More Power!!

Tinkster · 10-18-2004, 11:25 PM

You're very welcome :)

Cheers,
Tink