Bringing back no duplicates from mysql

[KevinM2k]

I have a database with records similar to below:

ID   | Resource Type ID | Name
-----------------------------------
1    | 1                | Record 1
2    | 3                | Record 1
3    | 7                | Record 1
4    | 1                | Record 2
5    | 5                | Record 3
6    | 5                | Record 3

I need to bring back the results of this database, however if there are 3 "Record 1" results as you can see above, it only brings back the one,

The results I want are as follows:

Name
------
Record 1
Record 2
Record 3

I can't use the "select distinct" as the resource type id is different therefore making the record different. is this possible??

[ I do realise that I should have created a link table in between the resource type table and the resource table however I have come into the project where it is already done like this and we are limited with time ]

Cheers

Kev

[stereofrog]

select distinct `name` from `table`

[lilleman]

Hi,

I think that it can be solved by using GROUP BY:

SELECT name FROM table GROUP BY name

[KevinM2k]

distinct would be good if i could say

select * from resources where (Name is Distinct)

As I need more information returned than just name.

Kev

[r937]

either of the above two solutions will give you distinct names

however, if you want any other column data to go along with the distinct names, then you will need something else

for example, if you want ResourceTypeID as well, then you have to have a rule to determinie which ResourceTypeID value you want for each name

here's an example to get the highest ResourceTypeID for each name --

select max(ResourceTypeID) as maxResourceTypeID 
     , Name
  from yourtable
group
    by Name

but now if you also want the ID that goes along with the highest ResourceTypeID, it gets even more complicated --

select ID
     , ResourceTypeID as maxResourceTypeID 
     , Name
  from yourtable as X
 where ResourceTypeID
     = ( select max(ResourceTypeID)
           from yourtable
          where Name = X.Name )

[KevinM2k]

there are 2 fields I need returning from the table:

national_document_name, national_document_link

the structure of my mysql database is

national_document_ID, (UNIQUE KEY)
rsa_id, (Resource Area ID)
rst_id, (Resource Type ID)
national_document_name, (Document Name)
national_document_link (The link to the document)

The first post shows an example of entries into the database.

I don't want any duplicate entries to show up as I'm only showing the document_name which can be clicked on which links to the document_link.

[KevinM2k]

Thanx everyone, I managed to get it working with the following sql:

SELECT DISTINCT * FROM [table name] GROUP BY national_document_name ORDER BY national_document_name ASC

I'll have to look up "GROUP BY".

Cheers

Kevin

[lilleman]

Hi,

I think that the following should give the desired result:

SELECT national_document_name
     , national_document_link
FROM table_name
GROUP BY national_document_name

Edit:

I didn't see your reply until now... I'm always too late.

[r937]

kevin, how is it possible that the same national_document_name can have more than one national_document_link?

and if it is possible, how do you decide which link you want for each name?????


please be aware that GROUP BY has certain rules and among them is that every non-aggregate in the SELECT list must also be in the GROUP BY clause

in particular this means that you cannot use GROUP BY if you write SELECT *

mysql is the single exception, it allows the rule for GROUP BY to be broken with reckless abandon

i caution you to learn how GROUP BY really works, or else you are in for some rude surprises if you ever try to write a GROUP BY in any other database

yours is wrong too, lilleman :-)

[stereofrog]

SELECT DISTINCT
      name, link
FROM table_name

returns all exisiting combinations of name and link. Dont know if this is portable...

[r937]

stereofrog, that is extremely portable, every database will support that query

[KevinM2k]

r937, I took what you had to say in, and i've used stereofrog's example and got it working with the following:

SELECT DISTINCT national_document_name, national_document_link FROM [table name] ORDER BY national_document_name ASC LIMIT 0, 10

Is this OK to use then or may I find other problems along the way??

Cheers

Kevin

[r937]

yes, that's fine

have you examined the output? do you have any names which have more than one link?

[lilleman]

Hi,

yours is wrong too, lilleman :-)

Aww, crap. How would a correct version of my query look?

[KevinM2k]

Yes I've checked R937 and that is fine, it did bring the same number of results back as lilleman's but as you said, that way probably wasn't the correct way of doing things.

Cheers

Kevin

[r937]

How would a correct version of my query look?

that depends on which link you wanted for each name

[lilleman]

Hi,

that depends on which link you wanted for each name

Then let us assume that a name can only have one link.

[r937]

well, that kinda goes against the entire thread, but if that's the case, then your query would be

SELECT national_document_name
     , national_document_link
FROM table_name

[lilleman]

Hi,

well, that kinda goes against the entire thread [...]

Yes, you're right. My bad.

[...], but if that's the case, then your query would be

SELECT national_document_name
     , national_document_link
FROM table_name

Well, the thing I didn't understand (and I wanted an example of) is the part you said about GROUP BY, and that all non-aggregates must be in it. Would this be correct to use, or am I still not understanding how it's done?

SELECT first_field
     , second_field
FROM table_name
GROUP BY first_field
       , second_field

[r937]

okay, examples

the following are all good --

select a ...
select sum(m) ...
select a ... group by a
select a,sum(p) ... group by a
select a,b ... group by a,b
select a,b,sum(q) ... group by a,b

the following are also good but not useful --

select a ... group by a,b
select a,sum(p) ... group by a,b
select a,b ... group by a,b,c
select a,b,sum(r) ... group by a,b,c

the following are not good --

select a,sum(p) ...
select a,b ... group by a
select a,b,sum(r) ... group by a

mysql allows these, and in my opinion shouldn't

[stereofrog]

well, that kinda goes against the entire thread, but if that's the case, then your query would be

SELECT national_document_name
     , national_document_link
FROM table_name

The name has only one link, but nobody said there is only one row for each name (== name-link combination). Therefore, DISTINCT is neccessary.

[r937]

if there is more than one row with the same name and link combination, then there is a problem, isn't there ...

... specifically, how that's possible when the document itself is unique

or do i not understand the reason for the unique key in this table?