Distinct problem

[Anon]

Hello,

I have the following problem:

When i issue

$sql = "SELECT row_id, distinct(datum), locatie, type, serial, regi, code, cn, unit, operator, note, nieuw, alias, dag_volg, not_new, fab_alias, prefix, cn_sort, looked_up, status, category FROM $table_name where locatie = trim(\"$locatie\") order by datum "; */

i get a Couldn't execute query

and when i change the distinct(datum) to
just datum, it works fine.

Can somebody shed a light on this ?

Greetings Chris

[[deleted]]

You can't use distinct on just one column.

what are you trying to do?

[Anon]

Aha, is that so !

Allright, what i am trying to do is to make a query which gives me just one record for one day instead for example 20 records on one date and 30 records on a second day.

So my statement is false ? I have also tried to issue distinct datum instead of distinct(datum) but that also doesn\'t work.

[[deleted]]

The problem is, you cannot use a distinct (or even a group by) if not all the columns you are selecting are in that distinct (or group by).

Distinct will return each value from a column exactly once.
You can use that to get a list of unique dates.

Now you want to list one row for each of those dates, so you added more columns.
But there are several rows for each date, so how is SQL supposed to know which of those records (that all have the same date) it should show?

[Anon]

I understand your point.

I've changed the statement to

$sql = "SELECT distinct datum FROM $table_name where locatie = trim(\"$locatie\") order by datum ";

Guess i am going to have to live with the absence of the other fields.

Thanks for your help.

Greetings Chris.