MySql simple question

[alex79]

let's say that i have a table with 3 collumns and 10 rows..
i want to select all the rows with 1 collumn different (no mether if collumns 2 and 3 are different or not)

how do i do that? with SELECT DISTINCT there are selected only rows wuth all 3 collomns different or how?

thx

[ZakAttack]

just download the mysql bible offa kazaa, i'm sure its in there if it exists, or mysql.com has extensive documentation.

[PSD]

Something like the following should work:

SELECT DISTINCT(YOURCOLUMNNAME) FROM YOURTABLENAME

[sweet7]

Originally posted by alex79
let's say that i have a table with 3 collumns and 10 rows..
i want to select all the rows with 1 collumn different (no mether if collumns 2 and 3 are different or not)

how do i do that? with SELECT DISTINCT there are selected only rows wuth all 3 collomns different or how?

thx

I don't understand the question. You want to select all the rows from a table with 1 colum different? Do you mean you want to exclude 1 column in your retrieval of information?

[alex79]

Originally posted by JCK
Something like the following should work:

SELECT DISTINCT(YOURCOLUMNNAME) FROM YOURTABLENAME

don't work.. i get an error like this:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource *******

any others ideeas?

[psili]

If you're getting an "not a valid mysql resource" then it looks like the connection to the DB is bad. A bad query would return another error code.

So i'd say that JCK's response would be fine:

select distinct() from table should work.

[alex79]

Originally posted by sweet7


I don't understand the question. You want to select all the rows from a table with 1 colum different? Do you mean you want to exclude 1 column in your retrieval of information?

let's say the columns are: date, name, location

i want retrire all the row with all thre informations (date, name, location) but name shuld not apeare 2 times..

if i use SELECT DISTINCT * FROM TABLE if there are 2 rows with same name but with different dates then they will be displayed
if i use SELECT DISTINCT name, location FROM TABLE then i will not be able to use the informations about date..

can somebody help me with the right sintax, pls?

[psili]

So

SELECT DISTINCT(name), date, location FROM tableName

doesn't work.

How about fuckin' around with Group BY clauses (mind i didn't check my syntax cuz i'm lazy)

SELECT name, date, location FROM tableName GROUP BY name, date, location

and see what your result set looks like.

[alex79]

Originally posted by psili
If you're getting an "not a valid mysql resource" then it looks like the connection to the DB is bad. A bad query would return another error code.

So i'd say that JCK's response would be fine:

select distinct() from table should work.

yes, it is works but in not what i want to do,.. i want display all the 3 colomns, not just the colomn with different resulats..
so, select distinct(colomn1) from table will display just the colomn1 informations, not all 3 colomns
i wnt display all the 3 colomns where one colomn is distinct

[dnsmonster]

Install PostgreSQL and use the real command that's designed for this kinda stuff: MINUS

SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items;

[alex79]

Originally posted by psili
So

SELECT DISTINCT(name), date, location FROM tableName

doesn't work.

How about fuckin' around with Group BY clauses (mind i didn't check my syntax cuz i'm lazy)

SELECT name, date, location FROM tableName GROUP BY name, date, location

and see what your result set looks like.

dosen't work

[buran]

I think you're looking for,


select distinct(name),date,location FROM tableName GROUP BY name;

You only want the group by on the name, since that's the one that you're making distinct.

mysql> create table test ( name char(20), date char(20), location char(20) );
Query OK, 0 rows affected (0.16 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)

mysql> show fields from test;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| date | char(20) | YES | | NULL | |
| location | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into test values ( 'buran', '12345', 'orlando' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ( 'buran', '67890', 'indiana' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values ( 'twinkley', 'abc123', 'michigan' );
Query OK, 1 row affected (0.00 sec)

mysql> select distinct(name),date,location from test group by name;
+----------+--------+----------+
| name | date | location |
+----------+--------+----------+
| buran | 12345 | orlando |
| twinkley | abc123 | michigan |
+----------+--------+----------+
2 rows in set (0.00 sec)

mysql>

[Mr. Porno King]

Originally posted by alex79


let's say the columns are: date, name, location

i want retrire all the row with all thre informations (date, name, location) but name shuld not apeare 2 times..

if i use SELECT DISTINCT * FROM TABLE if there are 2 rows with same name but with different dates then they will be displayed
if i use SELECT DISTINCT name, location FROM TABLE then i will not be able to use the informations about date..

can somebody help me with the right sintax, pls?

Your question doesn't quite make sense.

Lets say that you have these rows:

April 1, Jane, Toronto
April 3, Jane, Edmonton
April 5, Jane, Calgary
April 7, Bob, New York

--

okay.. obviously, you want to get back "April 7, bob, New York" but how do you want the database to choose which other row you get back?

You can make it choose at random, but keep in mind, the data you get back will be random too.