mysql group_concat issue

Question

I just trying to know is this possible the function of MYSQL GROUP_CONCAT to return this type of data. Here is a scenario

SELECT GROUP_CONCAT(marks) AS `i need only 40 int in this column`  FROM marks

when i execute this Query the result will be show like this

enter image description here

Result required 40

Hard code `40` in your query (`SELECT 40 FROM marks LIMIT 1`), unless you have a reason we could understand. — Salman A, Apr 04 '12 at 12:25
i have a multiple value in one column and i need to extract only 40 also i mentioned my Question is this possible or not @SalmanA — Query Master, Apr 04 '12 at 12:29
[`GROUP_CONCAT`](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) does not make any sense without `GROUP_BY` — knittl, Apr 04 '12 at 12:32
What is possible or not? Your question is totally unclear. Can you give a few sample rows and the expected output? If you only need a literal `40`, a simple `SELECT 40 LIMIT 1` will do. Otherwise you need to add a few more details to your question – as it stands it is totally unclear. — knittl, Apr 04 '12 at 13:13
@samad: The answer is `SELECT marks FROM marks WHERE marks = 40`. I cannot post it as an answer because your question and my answer does not make any sense. — Salman A, Apr 04 '12 at 15:24

score 3 · Accepted Answer · answered Apr 04 '12 at 12:24

3

Try this:

select group_concat(m.marks) from 
    ( select distinct marks from marks limit 40 ) m

answered Apr 04 '12 at 12:24

Pablo Santa Cruz

176,835
32
241
292

I am not sure I understood your question correctly though... :-) – Pablo Santa Cruz Apr 04 '12 at 12:26
@knittl: I don't know. To me, it seems that OP wants **only 40 integers**, and not **only integer 40**. But as I stated in my previous comment: don't know if understood correctly what OP wants. – Pablo Santa Cruz Apr 04 '12 at 12:28

score 1 · Answer 2 · answered Apr 04 '12 at 12:32

1

Advice first: normalize your database tables – a field should only contain a single value.

Now, the solution for your concrete problem: MySQL has the FIND_IN_SET function which should do what you want:

SELECT marks
FROM marks
WHERE FIND_IN_SET('40', marks)

answered Apr 04 '12 at 12:32

knittl

246,190
53
318
364

Thanx for sharing your experience but that not my Question – Query Master Apr 04 '12 at 13:00
@Samad: Then what exactly is your question?! – knittl Apr 04 '12 at 13:15
I still don't understand. `GROUP_CONCAT` creates a single string of multiple rows, when used in combination with `GROUP BY`. »[…] want to pick one element from comma separated field« ← which element? first? middle? last? matching a certain criteria? – knittl Apr 04 '12 at 13:37

score 0 · Answer 3 · edited Jul 01 '16 at 13:35

0

Your syntax

SELECT GROUP_CONCAT(marks) AS `i need only 40 int in this column`  FROM marks

is working properly. You are giving GROUP_CONCAT(marks) the name i need only 40 int in this column, so it shows what you are saying through syntax.

" Result required 40 "

What does it means when you use group_concat and you want to record where the marks are 40? Why not use query like

select * from table_name where marks='40'

If group_concat is a compultion, then use

SELECT GROUP_CONCAT(marks) AS `i need only 40 int in this column`  FROM marks where marks='40'

edited Jul 01 '16 at 13:35

ragingasiancoder

616
6
17

answered Apr 04 '12 at 12:42

Ankit Sharma

3,923
2
29
49

i have already mentioned my Question is this possible or not if possible then how – Query Master Apr 04 '12 at 12:59
your query is working properly what it have to, so whats the question arrived? means what do you whant to show in result. – Ankit Sharma Apr 04 '12 at 12:59
i want to get 40 value in this column with the help of mysql functions – Query Master Apr 04 '12 at 13:00
why not you using ` select * from table_name where marks='40' ` instead of group_concat,by the way why are you using group-concat, it will give you whole result not 40 only – Ankit Sharma Apr 04 '12 at 13:02
i know this statement but that not my Question – Query Master Apr 04 '12 at 13:04
Thanx for contribute your experience with me – Query Master Apr 04 '12 at 13:04
@Samad if group_concat is your cumpultion then see my last line added to this answer. – Ankit Sharma Apr 04 '12 at 13:06
let us [continue this discussion in chat because we cannot continue it here because of stackoverflow rules](http://chat.stackoverflow.com/rooms/9680/discussion-between-jason-vorhees-and-samad) – Ankit Sharma Apr 04 '12 at 13:11

mysql group_concat issue

3 Answers3