sub {
my $tags = shift;
my $output='';
my $db = $DB->table ('Links','Category','CatLinks');
my $cnt=0;
$db->select_options('ORDER BY Mod_Date DESC', 'LIMIT 10');
my $sth = $db->select ({Insider => 'Ces' }, ['Links.ID', 'Title','Full_Name','Portrait']);
while ( my($id,$tit,$fname,$portrait) = $sth->fetchrow_array) {
....
...


Do somebody know where i can put in an DISTINCT in the mysql query?

I think it would be something like this:

my $sth = $db->select ({Insider => 'Ces' }, ['DISTINCT(Links.ID)', 'Title','Full_Name','Portrait']);

Note that (as far as I know) the DISTINCT applies to the whole row - it doesn't matter which variable you put in the brackets.

DISTINCT (as well as other functions) behaves differently depending on the MySQL version.

If you think about what DISTINCT means, you'll understand that some of the ways you want it to behave, it can't -- logically.

SELECT DISTINCT(VALUE) will tell you how many DIFFERENT values there are for VALUE. It *obviously* can't return a row, because *which* row with that VALUE would/should it return?

One might argue it should return the most recent row... but again, without an index field or value, which row would that be?

To return DISTINCT rows, you need to do multiple queries, or perhaps a complex/compound query (later versions of MySQL support more of this).

You first select the values with DISTINCT, then pull out the row you want for each distinct value.

This works, for instance, if you have a database of animals, and you want to find out how many cats, dogs, etc other animals you have. If you do a DISTINCT(COUNT SPECIES) then you'd end up with a set of rows: dogs->23, cats->32, birds->21... etc. You would *not* get the row, only the value and count. Select DISTINCT (SPECIES) would return dog, cat, bird, etc.

I played with this for a long time, and eventually gave up. There must be a way to select the most recent (or whatever) row for a DISTINCT field value. I haven't figured it out though <G>.

But, where you say "DISTINCT applies to the whole row" is not quite true. It applies to a specific field, but pretty well ignores the rest of the row -- too much.

One way DISTNCT works, or may work, is if it's used in a table join, where one table is DISTNCTly selected for a value, which is then JOINed to another table.

I have not found a really good explanation, or set of examples for DISTINCT in any of the docs or tutorial sites, or I'd put a reference here.

The above is *not* gospel <G> If someone has a really good explanation, I'd like to know it too.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Wow. Good thank we have you here, Robert. ;-)
Unfortunately i am ill, very tired and have to work at home on this fu**ing 1280*1050 Laptop,
where my eyes burn after one or two hours looking on it. So i really dont understand one word today.
But i will read it tomorow a second time.

All i know is that with my query i got 10 links, but if one of them resides in more than one cat i get it twice.
So i remember the DISTICNT to get it only once. Now i will give it a try, then back to the bed. ;-(

Thank you.

my $sth = $db->select ({Insider => 'Ces' }, ['DISTINCT(Links.ID)', 'Title','Full_Name','Portrait']);

is wrong because then the query looks for a field with name DISTINCT(Links.ID) ...

Hi,

I just wrote a reply, and it crashed. Here's a short version, that probably covers it.



>> my $sth = $db->select ({Insider => 'Ces' }, ['DISTINCT(Links.ID)', 'Title','Full_Name','Portrait']);


Won't work. You are asking for a DISTINCT ID, but also other values that would be in a "random" row. The SQL engine can't do that.

You'd need to select DISTINCT (ID) then do a second query for each of those ID's on an indexed database, that was organized the way you wanted it, then with a group by /order by construct you'd do your select ID limit 1 to pull off the row you want.

DISTINCT can't return rows based on one value, logically it won't work. It can return unique values for a specific field, that can be used to pick off rows you want.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

From mysql.com:

Hi,

Ok, we are splitting hairs here.

If you give DISTINCT a field, *that* is what is compared. If you don't, it will pick out rows that are distinct. But, in a Links table _all_ rows are distinct, because of the ID field, so all rows will be returned. In the Users table, the same applies. In the sessions, *_track, etc tables, the same will apply. There are no duplicate rows in Links. Duplicate rows are obviated by using count fields.

If you try to return a row based on a single DISTINCT value, it *can't* happen. *WHICH* row do you return???

If you want to just return distinct rows, that's another story -- but that has virtually *no* [logical] application in a Links system, because *all* rows are distinct by nature.

Is this making any sense? What people want to do, return the first, last, etc ROW with a certain value, and no other rows with that value in a SPECIFIC FIELD, *IS*NOT* how DISTINCT works.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

I think I'm beginning to see that the confusion is in what I meant by 'row'. Having realised this and rereading your comments I think I agree with you. When I said 'whole row' I meant the fields that you asked for in the select statement rather than the whole row of the table (and I think that is also what the mysql quote means aswell).

Hopefully this is a better explanation of what I was meaning in my original post:

If you use DISTINCT(Name) then a select statement will give you all the rows which have a different value for Name. So this will give you a list of all the different values for the Name field in your table.

If you use ['DISTINCT(Name)','Age'] then the select statement will give you all the rows which have a different value for the pair (Name,Age). If all your different Names have the same value for Age (i.e. all rows where Name is Sarah have Age is 25) this will return exactly the same rows as DISTINCT(Name) but it will return both the values for Name and Age rather than just Name - which can be very useful.

The problem arises when you have the same Name value with different Age values - in this case you will not get the same rows as DISTINCT(Name) - you will ALSO get all the rows where the same Names have different Ages.

So it doesn't matter which field you put in the bracket - ['Name','DISTINCT(Age)'] will give exactly the same rows as ['DISTINCT(Name),'Age'].