select all with distinct

[dreamwest]

Im trying to select distinct title from a column but also need to select all columns

 

Heres what ive originally got, this has duplicates in title column:

 

$sql = "SELECT * from table where (title like '%{$search_id}%') ";

 

If i select distinct info from the other columns wont get selected:

$sql = "SELECT DISTINCT title from table where (title like '%{$search_id}%') ";

 

How can i select distince titles and also select all coumns with distinct titles in one query

[Mchl]

What exactly do you want to achieve?

$sql = "SELECT *, DISTINCT title from table where (title like '%{$search_id}%') ";

should work... although I'm not sure if this is what you want.

[Ken2k7]

SELECT DISTINCT title, colname1, colname2
FROM table
WHERE title LIKE '%{$search_id}%'

 

List them like that. Only way I know. DISTINCT doesn't like to work with *.

[PFMaBiSmAd]

DISTINCT is not a function that applies to the column following it. When it is present in the select term, it causes duplicate (with exactly the same values in everything that is selected) rows in the result set to be removed.

 

Could you post an example of the data you have and what results you want? Do you have multiple rows with the same title but with other unique values for the other columns and if so, which of the other unique values do you want to select?

[Mchl]

DISTINCT is not a function that applies to the column following it.

 

Oh great. That explains why it never worked like I expected it to XD

[dreamwest]

What exactly do you want to achieve?

$sql = "SELECT *, DISTINCT title from table where (title like '%{$search_id}%') ";

should work... although I'm not sure if this is what you want.

 

I get an error when i try this

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT title from table where (title like '%search icon%') order by title' at line 1

 

I need to select over 20 columns so i cant keep naming each one

 

SELECT DISTINCT title, colname1, colname2, etc
FROM table
WHERE title LIKE '%{$search_id}%'

 

 

[Mchl]

Why not?

[dreamwest]

Why not?

 

Too much code. But if theres no other way...

[PFMaBiSmAd]

The following is the syntax prototype for a SELECT statement. Among other things, it specifies the order in which elements must be listed. DISTINCT must immediately follow the SELECT keyword -

 

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [sTRAIGHT_JOIN]

      [sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]

      [sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS]

    select_expr [, select_expr ...]

    [FROM table_references

    [WHERE where_condition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_condition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC], ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [iNTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

    [FOR UPDATE | LOCK IN SHARE MODE]]

 

SELECT DISTINCT * is valid syntax.

 

Also -

The ALL, DISTINCT, and DISTINCTROW options specify whether duplicate rows should be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and specify removal of duplicate rows from the result set.

 

[fenway]

Should be... it's simply a modifier to SELECT.