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

select distinct with conditions attached

100+
P: 170
I'll try to explain the problem, but it may not make sense.

I have a mysql table with columns like id,title,speaker,day,month,year, topic etc.

At present, my syntax is
Expand|Select|Wrap|Line Numbers
  1. select * from files order by year, month, day
What this does is return all the entries as requested. However, initially, when an entry is made, it can have up to 3 distinct topics associated with it. So in the table, id1, id2 and id3 will have exactly the same info in every column except for the topic column.

Obviously, when I run the above select statement it prints out the details for each entry, id1, id2 and id3 as separate entries (as it's supposed to).

What I want is for mysql to look for any entries that are identical except for topic. And then instead of listing all 3 entries, list only 1 entry. When I print to the screen the information on topic, it would say "health, wealth, happiness".

At the moment there are 3 distinct entries. The first will say topic: "health". The second "wealth" etc etc.

I can't just use a select distinct title statement, as its possible that there may be two entries with exactly the same title, but different day, month and year.

Below I've put what it currently prints and what I need it to print. Hopefully someone(s) can interpret my request and point me in the right direction.

Thanks.

Currently:

Title: Making Money
Date: 07/09/28
Topic: money

~~~~~~~~~~~~~~~

Title: Making Money
Date: 07/09/28
Topic: happiness


What I need:

Title: Making Money
Date:07/09/28
Topic: money, happiness
Nov 19 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 102
Currently:

Title: Making Money
Date: 07/09/28
Topic: money

~~~~~~~~~~~~~~~

Title: Making Money
Date: 07/09/28
Topic: happiness


What I need:

Title: Making Money
Date:07/09/28
Topic: money, happiness
Hi,
As per my understanding about your problem, this is what you are looking for:

Expand|Select|Wrap|Line Numbers
  1. Select title, date, GROUP_CONCAT(topic)
  2. From <your_table>
  3. Group By title, date
  4. Order By title, date
  5.  
Let me know if you were looking for something else.

-Pradeep
Nov 20 '07 #2

100+
P: 170
Hi,
As per my understanding about your problem, this is what you are looking for:

Expand|Select|Wrap|Line Numbers
  1. Select title, date, GROUP_CONCAT(topic)
  2. From <your_table>
  3. Group By title, date
  4. Order By title, date
  5.  
Let me know if you were looking for something else.

-Pradeep

Just about there...

My statement is now "select *, GROUP_CONCAT(topic) from files group by title order by year, month, day"

It has successfully eliminated the repeated entries. However, next to Topic: it only shows the first topic, not the others.

So now it looks like...

Title: Making Money
Date:07/09/28
Topic: money

instead of what I need it to look like:

Title: Making Money
Date:07/09/28
Topic: money, happiness

I figure it's something minor to get that working.

Thanks so far for your help and I'm looking forward to the solution.
Nov 20 '07 #3

Expert 100+
P: 102
Just about there...

My statement is now "select *, GROUP_CONCAT(topic) from files group by title order by year, month, day"

It has successfully eliminated the repeated entries. However, next to Topic: it only shows the first topic, not the others.

So now it looks like...

Title: Making Money
Date:07/09/28
Topic: money

instead of what I need it to look like:

Title: Making Money
Date:07/09/28
Topic: money, happiness

I figure it's something minor to get that working.

Thanks so far for your help and I'm looking forward to the solution.
Hi beary,
Please include "date" column in Group By clause and also make sure you have GROUP_CONCAT() function in the Select clause.

For more details on GROUP_CONCAT() please refer:
GROUP_CONCAT

In case of further doubts plz get back.

Thanks,
pradeep
Nov 20 '07 #4

100+
P: 170
Hi beary,
Please include "date" column in Group By clause and also make sure you have GROUP_CONCAT() function in the Select clause.
It now reads "select *, GROUP_CONCAT(topic) from files group by year, month, day, title order by year, month, day"

Still no change...

I am clearly doing something wrong, so I thought I'd post the sql for creating the table, and inserting the records followed by my required output. Perhaps you can create the statement which will work after testing it. (If you would be so good!)

CREATE TABLE `files` (
`id` tinyint(6) NOT NULL auto_increment,
`year` text NOT NULL,
`month` text NOT NULL,
`day` text NOT NULL,
`series` text NOT NULL,
`title` text NOT NULL,
`speaker` text NOT NULL,
`topic` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO `files` (`id`, `year`, `month`, `day`, `series`, `title`, `speaker`, `topic`) VALUES
(12, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'money'),
(13, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'wealth')

What I need displayed is:

Title: Money and happiness
Date: 07/01/01
Speaker: J Avery
Topic(s): money, wealth

Any hope?
Nov 20 '07 #5

Expert 100+
P: 102
It now reads "select *, GROUP_CONCAT(topic) from files group by year, month, day, title order by year, month, day"

Still no change...

I am clearly doing something wrong, so I thought I'd post the sql for creating the table, and inserting the records followed by my required output. Perhaps you can create the statement which will work after testing it. (If you would be so good!)

CREATE TABLE `files` (
`id` tinyint(6) NOT NULL auto_increment,
`year` text NOT NULL,
`month` text NOT NULL,
`day` text NOT NULL,
`series` text NOT NULL,
`title` text NOT NULL,
`speaker` text NOT NULL,
`topic` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO `files` (`id`, `year`, `month`, `day`, `series`, `title`, `speaker`, `topic`) VALUES
(12, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'money'),
(13, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'wealth')

What I need displayed is:

Title: Money and happiness
Date: 07/01/01
Speaker: J Avery
Topic(s): money, wealth

Any hope?
Hi beary,
I checked it with your table structure - the query works absolutely FINE.

Please try the following:
Expand|Select|Wrap|Line Numbers
  1. select year, month, day, title, speaker, GROUP_CONCAT(topic) as TOPICS 
  2. from files group by year, month, day, title 
  3. order by year, month, day
  4.  
Your topics "money,wealth" will be under TOPICS column.

- Pradeep
Nov 21 '07 #6

100+
P: 170
Hi beary,
I checked it with your table structure - the query works absolutely FINE.

Please try the following:
Expand|Select|Wrap|Line Numbers
  1. select year, month, day, title, speaker, GROUP_CONCAT(topic) as TOPICS 
  2. from files group by year, month, day, title 
  3. order by year, month, day
  4.  
Your topics "money,wealth" will be under TOPICS column.

- Pradeep
Pradeep,

Thankyou so much for sticking with me on this one! It now works perfectly, as you said. It's nice to learn a completely new (to me) and very cool mysql thing.

I was wondering why it wouldn't work for me. The only essential difference between your first post in response and the last one is that in the last one you had group_concat(topic) as topics, whereas in the 1st the phrase "as topics" was not there. Perhaps that has been the issue.

Regardless, I'm grateful for your help!
Nov 21 '07 #7

Expert 100+
P: 102
Pradeep,

Thankyou so much for sticking with me on this one! It now works perfectly, as you said. It's nice to learn a completely new (to me) and very cool mysql thing.

I was wondering why it wouldn't work for me. The only essential difference between your first post in response and the last one is that in the last one you had group_concat(topic) as topics, whereas in the 1st the phrase "as topics" was not there. Perhaps that has been the issue.

Regardless, I'm grateful for your help!
Your Welcome!

Glad to have helped you.

Rgds,
Pradeep
Nov 21 '07 #8

Post your reply

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