2

Please note this is regarding a homework problem, the homework is done and works to the spec, but it's slow so I'm trying to lessen how many SQL calls I make. And I'm still learning my SQL-fu.

My table looks like:

Ticker |Name                  |Industry
A      |Agilent Technologies  |Information Technology
AA     |Alcoa Inc             |Materials
AAPL   |Apple Inc.            |Information Technology

Right now I am running a SQL statement like this in a loop. Each iteration I change which industry to look at.

SELECT ticker FROM company WHERE industry = 'Information Technology'

But that means I have to make a call to the database for each industry just to get the tickers related to it. What I want is to make one database call that returns multiple results. So the first result would be a list of all the tickers for the first industry, the second result would be all the tickers for the second industry etc.

I tried this SQL, but it just gave me one ticker for each industry, not a full list

SELECT ticker, industry FROM company GROUP BY industry
Improve this question
7
  • 1
    BTW, thanks for clarifying what this is for and what you have done. It makes (at least me) more willing to help out knowing you are attempting to learn more, not just get me to do your homework for you. Commented Jun 2, 2011 at 4:26
  • 1
    what is the problem with the query you used? are you trying to get all "ticks" for an industry in just one row? Commented Jun 2, 2011 at 4:27
  • That sounds right to me, have you tried ORDER BY? Commented Jun 2, 2011 at 4:29
  • @BiggsTRC, thank you, just trying to be honest and up my skillz Commented Jun 2, 2011 at 4:30
  • 1
    @jb: Probably what Nate was alluding to was what BiggsTRC answered. Commented Jun 2, 2011 at 4:48

2 Answers 2

Reset to default
7

This should work for you:

SELECT GROUP_CONCAT(Ticker) as Tickers, industry FROM company GROUP BY industry
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

never heard of GROUP_CONCAT() before. +1 just for that.
But, if what you are looking for is performance, then the "correct" way of doing things is normalize your database (separating the industries in another table).
No doubt he'd want to specify the separator so that it can be split again.
+1: I did a simple benchmark. On iterations over 10000 rows (only 9 groups), group_concat() is almost 5 times faster than iterating all rows. The big thing to watch out for is that the result of group_concat() is truncated to group_concat_max_len, and is also limited by max_allowed_packet. dev.mysql.com/doc/refman/5.1/en/…
1

Unfortunately, you are probably doing it the best way if you need to have separate result sets for each industry. However, if you want to do a little manipulation in your code, you could return the entire table ordered by the industry then the ticker. Then in your code you could read until the industry changed and then do whatever magic you want and then read again until the industry changed again. Basically, do your loop on the result set instead of using your loop to make database calls. If you did it this way, your query would look like this:

SELECT ticker
FROM company
ORDER BY Industry, Ticker

You would make one call to the database and do your looping through the results in your code (or wherever you are working on your data).

Improve this answer

4 Comments

hmm, not as slick as I was hoping for, but it would do as a work around. +1 to you sir.
See @PachinSV solution - it is the solution
@Bohemian - This would be true if there is only the one field that will ever be returned and if you wanted all of the data in one row (instead of the way the system is currently written, which is to process multiple rows per industry). I would say that PachinSV has a good answer for some cases, but not for others.
@jb: Often, clear-cut is better than slick. Unless slick provides much better performance, in which case you do the slick version and document it. And there usually isn't anything slick about getting stuff out of a database; most of it is straightforward.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.