On the SQL statement below, I am trying to get the d.group_descr distinct because there are duplicates for this field but when I run the query in SQL it gives me dups. Does anyone have any ideas on  how to write this statement to get just the d.group_descr to show distinct but not the other fields in the query.

Hi,bootzilla

Try this

PLZ MARK AS ANSWER IF IT HELP U.

THANKS.

raghav_khunger:

Hi,bootzilla

Try this

 

 

SELECT distinct d.group_descr

FROM icc_user_data AS a LEFT OUTER JOIN icc_users AS b ON a.user_id = b.user_id

LEFT OUTER JOIN icc_users_groups AS c ON a.user_id = c.user_id

LEFT OUTER JOIN icc_groups AS d ON c.group_id = d.group_id

WHERE (b.user_id NOT IN (249)) AND (d.group_id IS NOT NULL) ORDER BY d.group_descr

 

 

 

 

PLZ MARK AS ANSWER IF IT HELP U.

THANKS.

 

Thanks but this is not the problem. The problem is when I put in the other fields I don't get a distinct value for the group_descr field. My statement has to include the other fields but group_descr has to be distinct. Follow me?

My statement has to include the other fields but group_descr has to be distinct. Follow me? 

But that is impossible and doesn't make sense.  For example, using this simple table which ID will be returned for GroupId A.

 Id   GroupId

1      A

2      A

3      A

Distinct is based on the entire row, not a single field.  Select Distinct GroupId, Id will return all records from the above table.

Now you could use the Group By clause-  Select GroupId, Max(Id) Group By GroupId, but that returns a single record (A, 3).

When I run the query below here is an example of the results I get:

SELECT DISTINCT d.group_descr, a.first_name, a.last_name, a.user_id, b.email, d.group_id
FROM         icc_user_data AS a LEFT OUTER JOIN
                      icc_users AS b ON a.user_id = b.user_id LEFT OUTER JOIN
                      icc_users_groups AS c ON a.user_id = c.user_id LEFT OUTER JOIN
                      icc_groups AS d ON c.group_id = d.group_id
WHERE     (b.user_id NOT IN (249)) AND (d.group_id IS NOT NULL)
ORDER BY d.group_descr

See ACE USA(group_descr) field is not a distinct value. Anything with multiple records doesn't give me a distinct value. That is the problem I am having. If I remove all other fields and just select group_descr I get a distinct value but when I add the other fields to the statement it removes the distinct for group_descr.

Exactly.  So which values of the "other" fields should be returned for the "Ace USA" group_descr.  Kim's, Paul's, Twanda's or Walter's?

All of them should be returned but should show as one on a web page. Maybe my question is more web related than sql related. I am trying to get to show like this:

It seems your query returns are right, but they are not what you want. Here is the question for you, if you want only one record among the four ACE USA records, which column(s) should we use to remove other three.

You can use a ranking function to to get the top 1 row of each group, so your returning records will be unique.

SELECT  group_descr, first_name, last_name, user_id, email, group_id FROM (SELECT  d.group_descr, a.first_name, a.last_name, a.user_id, b.email, d.group_id, ROW_NUMBER() OVER(PARTITION BY  d.group_id ORDER BY a.user_Id DESC) as rn
FROM         icc_user_data AS a LEFT OUTER JOIN
                      icc_users AS b ON a.user_id = b.user_id LEFT OUTER JOIN
                      icc_users_groups AS c ON a.user_id = c.user_id LEFT OUTER JOIN
                      icc_groups AS d ON c.group_id = d.group_id
WHERE     (b.user_id NOT IN (249)) AND (d.group_id IS NOT NULL)
ORDER BY d.group_descr

) t

WHERE rn=1

You should do this formatting on your page, for example, you can format it the way you want in a gridview's databind event or use a nested data control. You can search this forum for that information.

bootzilla:

Maybe my question is more web related than sql related.

Yes. The application should display the query results in whatever format is required.