select distinct and non-distinct collums

[pornflakes]

Hi guys,

 

I´ve got another yet even funnier problem, which I cannot solve

 

MySQL server version 5.3.0

 

CREATE TABLE `test` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`code` int(15) NOT NULL,
`action` varchar(30) NOT NULL,
`date` varchar(20) NOT NULL,
`insert` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

 

I need to select distinct action column and non-distinct id column corresponding to it.

 

I´ve tried :

SELECT DISTINCT ON (action) id, action FROM test GROUP BY id;

 

and other variations but none of them seems to work...

 

Thanks in advance

[pornflakes]

Some data from the table

 

id code action date                 insert

1 655 nope     12/08/2009 21:03 Luke

2 655 yes       12/08/2009 21:03 dude1

3 700 omg wtf 13/08/2009 19:59 Smith

4 725 nope     15/08/2009 11:58 dude1

 

I need to select action and id but I need only action to be distinct.

Why can´t simple query like this work?

SELECT id, (DISTINCT action) FROM test

 

I have no idea how to do this, I was thinking maybe create second table with only id and action and joining them 'somehow' but again I´m new to this and I wouldn´t know how to join them...

I will greatly appreciate any help

[kickstart]

Hi

 

Not quite sure what you want output?

 

For example, if you data was:-

 

id    code    action    date                    insert

1    655    nope        12/08/2009 21:03    Luke

2    655    yes          12/08/2009 21:03    dude1

3    700    omg wtf    13/08/2009 19:59    Smith

4    725    nope        15/08/2009 11:58    dude1

5    735    nope        15/08/2009 11:58    dude2

6    745    nope        15/08/2009 11:58    dude3

 

It appears that id is unique, but if you want a distinct action which id would it refer to?

 

All the best

 

Keith

 

 

 

[pornflakes]

Hi

 

Not quite sure what you want output?

 

For example, if you data was:-

 

id    code    action    date                    insert

1    655    nope        12/08/2009 21:03    Luke

2    655    yes          12/08/2009 21:03    dude1

3    700    omg wtf    13/08/2009 19:59    Smith

4    725    nope        15/08/2009 11:58    dude1

5    735    nope        15/08/2009 11:58    dude2

6    745    nope        15/08/2009 11:58    dude3

 

It appears that id is unique, but if you want a distinct action which id would it refer to?

 

All the best

 

Keith

 

Hi,

 

Hmmm... That seems to be a problem. I didn´t see that.

Is there even a way to do what I want to do?

I want it to select "nope, yes, omg wtf" but with all the id´s

basicaly I need something simple to identify the action with, which I could use in html form to do another search for the action

[kickstart]

Hi

 

A simple select would give you all the actions and their ids, but not sure it would be meaningful. Eg, in the data above you would get 4 rows with an action of nope.

 

SELECT action, id FROM test ORDER BY action, id

 

It would appear that you would more likely just want a list of actions and later process all the ids with a matching action.

 

If you want a list of actions with all the ids for that action on one line then somethign like this:-

 

SELECT action, GROUP_CONCAT(CAST(id AS CHAR)  SEPARATOR ',') FROM test GROUP BY action

 

However that will return a comma seperated list of ids for each action which isn't convenient to process.

 

All the best

 

Keith

[pornflakes]

Thanks a lot for your time kickstart.

I will have to figure some way around it 

[kickstart]

Hi

 

What do you want to do with it and how do you want to use it? Ie, but wider idea of what you are trying to achieve.

 

Hopefully we can help you .

 

All the best

 

Keith

[pornflakes]

I'm trying to fill this form with data from the table

<form method="post" action="search.php">
    Choose action:<br>
   <select name="laction">
    <?php
   $query2 = mysql_query("SELECT DISTINCT id, action FROM test ORDER BY id");
   while($col2 = mysql_fetch_array($query2))
   {
      echo "<option value=" . $col2['id'] .">" . $col2['action'] . "</option>";
   }
     ?>
        </select>
    <input type="submit" value="Search" />
    </form>

 

I figured I can't use

<?php
   $query2 = mysql_query("SELECT DISTINCT action FROM test ORDER BY id");
   while($col2 = mysql_fetch_array($query2))
   {
      echo "<option value=" . $col2['action'] .">" . $col2['action'] . "</option>";
   }
?>

 

because then if the action column value in the database has more than one word stored in it, separated by space, then this search :

 

<?php
$laction=@$_POST["laction"];

if (isset($laction)){
   
   $result = mysql_query("SELECT * FROM test WHERE (action) =  ('$laction') ORDER BY id");   
}
?>

gives me no result.

I figured the problem is that it sends only the first word from the $laction variable for the search.

 

The form gets all the values from the table and fills the <options>. But when I want to do the search for lets say "omg wtf" it will only do the search for "omg".

[kickstart]

Hi

 

It would appear that you want a list of actions and a random id that belongs to them. At this stage you do not care about the actual id as it refers to the rest of the record, just the action it links to.

 

Ie, you might provide a list of actions and when someone selects one you provide a list of (say) the ids that match.

 

Firstly you would probably be better off having a seperate table of actions, with your current table referring to the id of an action on the action table.

 

However with your current list for your list of actions you probably want the distinct actions and for each one some id that matches one of them. Possibly easiest to just use the max id for any action:-

 

SELECT action, MAX(id) FROM test GROUP BY action

 

You will then have to present a list of matching rows from test for the action the user selects.

 

Hope that gives you some ideas.

 

All the best

 

Keith