SQL Server, SQL Server Express, and SqlDataSource Control

Re: Need help with Select distinct SQL statement

brucevde — Mon, 10 Nov 2008 21:46:59 GMT

bootzilla:

Maybe my question is more web related than sql related.

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

Re: Need help with Select distinct SQL statement

limno — Mon, 10 Nov 2008 21:43:49 GMT

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.

Re: Need help with Select distinct SQL statement

limno — Mon, 10 Nov 2008 21:40:10 GMT

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

Re: Need help with Select distinct SQL statement

bootzilla — Mon, 10 Nov 2008 21:37:00 GMT

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:

Aasgard Summit	Lucia	Udlinek	239	luciau@aasgardsummit.net	140
Abercrombie Simmons and Gillette of VA	D.R.	Straeten	371	dstraeten@asg-adj.com	244
ACE USA	Kim	Mayes	195		94
	Paul	Keane	277	Paul.keane@ace-ina.com	94
	Twanda	Martin	258	twanda.martin@ace-ina.com	94
	Walter	Novak	399	Walter.Novak@ace-ina.com	94

Re: Need help with Select distinct SQL statement

brucevde — Mon, 10 Nov 2008 21:30:44 GMT

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?

Re: Need help with Select distinct SQL statement

bootzilla — Mon, 10 Nov 2008 21:22:14 GMT

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

Aasgard Summit	Lucia	Udlinek	239	luciau@aasgardsummit.net	140
Abercrombie Simmons and Gillette of VA	D.R.	Straeten	371	dstraeten@asg-adj.com	244
ACE USA	Kim	Mayes	195		94
ACE USA	Paul	Keane	277	Paul.keane@ace-ina.com	94
ACE USA	Twanda	Martin	258	twanda.martin@ace-ina.com	94
ACE USA	Walter	Novak	399	Walter.Novak@ace-ina.com	94

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.

Re: Need help with Select distinct SQL statement

brucevde — Mon, 10 Nov 2008 21:17:01 GMT

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).

Re: Need help with Select distinct SQL statement

bootzilla — Mon, 10 Nov 2008 21:00:18 GMT

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?

Re: Need help with Select distinct SQL statement

raghav_khunger — Mon, 10 Nov 2008 20:48:11 GMT

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.

Need help with Select distinct SQL statement

bootzilla — Mon, 10 Nov 2008 20:29:01 GMT

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.

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"