DISTINCT with two items ...

[Avihai]

What happens if I have this:


SELECT DISTINCT name, id FROM table WHERE section=4 AND ORDER by name ASC


and I want only the name to be DISTINCT and the id as usual?

With the code as is everything jumps one row :-(

Thanks,
Avihai

[cs3mw]

you will have to explain yourself further here because I dont see why you would want to do this. It sounds like you havent normalised your table properly because what I think your asking for is a list of ids associated with that name inwhich case you are repeating information.

AS I say I may be wrong but if you send me your table structure with details il have a look for you.

Mike

[chazzy]

i think you want something more like


select name,max(id) from table where section=4 group by name order by name asc;

[Avihai]

First thanks for the quick replies.

I have a table that has names and id.
With one script I pull out all the names and I put the results in a combo box.

The thing is that it just might be that a name would appear twice on the db and I want to show it on the combo box only one time. In order for the combo box to work with the javascript that I've written I an to assosiate the proper id to the name.

When there is a duplicated name it has a different id and that is the problem.
If I use distinct for the names on the id side there are no duplicates.

The code for pulling the names:

SELECT DISTINCT name FROM table WHERE sectionid=4 AND state=1 ORDER by name ASC


code for the option tag:


<option value="'.$row3['id'].'">' . $row['name'] .'</option>';


I guess that my question would be what should I do when there are duplicates on names but not in is?

I hope that helps.

Thanks,
Avihai

[cs3mw]

So what is your primary key is it ids or name? I take it will be ids because you cant have duplicate names and thats the problem your faced with.

Secondly is it possible for you to use ajax, because this would make it extremely simple. If so this will be an excellent example

http://www.dhtmlgoodies.com/index.html?whichScript=ajax_chained_select

I may be wrong but the way Im reading it, you want to have two select boxes. The first is name. When the name is selected you want all ids to be located in the second box associated with the name in the first. Is this correct?

[Avihai]

So what is your primary key is it ids or name? I take it will be ids because you cant have duplicate names and thats the problem your faced with.

Secondly is it possible for you to use ajax, because this would make it extremely simple. If so this will be an excellent example

http://www.dhtmlgoodies.com/index.html?whichScript=ajax_chained_select

I may be wrong but the way Im reading it, you want to have two select boxes. The first is name. When the name is selected you want all ids to be located in the second box associated with the name in the first. Is this correct?

The name is for the display in the combo and the id is used to construct the link to the content page.

I do no not need tow combos or ajax here. Just to grab a distinct name and it's associated id.

I have it working by now for the names ... the thing it that the ids are mixed up because I cannot use distinct for the ids for there are no duplicated.

I have one SQL for the names and another for the id.

I have tried:


SELECT DISTINCT name, id FROM table WHERE sectionid=4 AND state=1 ORDER by name ASC


And I still get duplicates but with the correct ids.

Any idea???

[cs3mw]

well what I would do is have the name as the primary key which will prevent the user from using that name again. Failing that if you have already populated the table and cannot change it (which I would defintely recommend) then you could use something like this

SELECT DISTINCT name, id FROM table WHERE sectionid=4 AND state=1 ORDER by name ASC LIMIT 0, 1

however this will only give you back the first id that is entered on the table.

Hope this helps its a bit arkward and i would defintely recommen doing it the first way