By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

Avoid column duplicate printing in mysql

P: 2
I have a Mysql table that looks like this:
model text(10) the moodel of item
color text(10) the color of item

i have some records:
1) "pippo";"red"
2) "pippo";"yellow"
3) "pippo";"green"
4) "gino";"blue"
4) "gino";"red"
4) "gino";"black"

if i do this query
select name, color from table order by 1,2
I will have this results:
Expand|Select|Wrap|Line Numbers
  1. name           color
  2. gino           black
  3. gino           blue
  4. gino           red
  5. pippo          green
  6. pippo          red
  7. pippo          yellow
  8.  
I would like to have
Expand|Select|Wrap|Line Numbers
  1. name           color
  2. gino           black
  3.                blue
  4.                red
  5. pippo          green
  6.                red
  7.                yellow
  8.  
Is there a mysql whai to have this results?

Thanks!
Dec 10 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,364
You will need to number the rows and partition it by the name.

If you do not know how to do that, you can use one of these methods:
http://www.xaprb.com/blog/2005/09/27...mber-function/
http://www.xaprb.com/blog/2006/12/02...rows-in-mysql/

You can then use a CASE statement to show the name only if the row number is one.
Dec 10 '12 #2

P: 2
Thanks!!
Using the examples in your link I've got what i need!!
Expand|Select|Wrap|Line Numbers
  1. set @type = '';
  2. set @num  = 1;
  3.  
  4. select
  5.    @num := if(@type = type, "", type) as row_number,
  6.    @type := type as dummy,
  7. variety
  8. from fruits;
That give me this results:
Expand|Select|Wrap|Line Numbers
  1. +------------+--------+------------+
  2. | row_number | dummy  | variety    |
  3. +------------+--------+------------+
  4. | apple      | apple  | fuji       |
  5. |            | apple  | gala       |
  6. |            | apple  | limbertwig |
  7. | cherry     | cherry | bing       |
  8. |            | cherry | chelan     |
  9. | orange     | orange | navel      |
  10. |            | orange | valencia   |
  11. | pear       | pear   | bartlett   |
  12. |            | pear   | bradford   |
  13. +------------+--------+------------+
  14.  
  15.  
Dec 11 '12 #3

Rabbit
Expert Mod 10K+
P: 12,364
Glad you got it working, good luck with the rest of your project.
Dec 11 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.