LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
LinkBack Search this Thread
Old 10-06-2004, 09:40 PM   #1
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Rep: Reputation: 15
SQL query help pls.


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?
 
Old 10-06-2004, 11:56 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,950
Blog Entries: 11

Rep: Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860
Where are you adding the grouping?


Cheers,
Tink
 
Old 10-07-2004, 05:58 AM   #3
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
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;
 
Old 10-08-2004, 04:44 PM   #4
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,950
Blog Entries: 11

Rep: Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860
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
 
Old 10-14-2004, 12:13 PM   #5
Thomasseafort
LQ Newbie
 
Registered: Oct 2004
Location: Fort Wayne IN
Distribution: Fedora, 2000pro, server
Posts: 26

Rep: Reputation: 15
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
 
Old 10-18-2004, 05:20 AM   #6
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
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!!
 
Old 10-18-2004, 08:23 AM   #7
Thomasseafort
LQ Newbie
 
Registered: Oct 2004
Location: Fort Wayne IN
Distribution: Fedora, 2000pro, server
Posts: 26

Rep: Reputation: 15
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.
 
Old 10-18-2004, 08:51 PM   #8
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Original Poster
Rep: Reputation: 15
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!!
 
Old 10-18-2004, 11:25 PM   #9
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,950
Blog Entries: 11

Rep: Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860Reputation: 860
You're very welcome :)


Cheers,
Tink
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query performance smaida Programming 6 06-08-2005 09:22 AM
Query tool for MS-SQL server dunric Linux - Software 0 11-14-2004 11:39 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 04:24 PM
SQL Query question oulevon Programming 7 01-16-2004 01:50 AM
SQL query, comparing tables ngomong Programming 3 07-07-2002 07:44 PM


All times are GMT -5. The time now is 08:27 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration