Using Distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Distinct

Author

Topic

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-22 : 16:20:33

I am trying to pull information out of a table using distinct, but instead of just pulling a certain column I want to pull multiple columns in a row?

However when I use the command below I only get the "workitem_number" column available, where there are approx. 4 other columns that I need (workitem_title, workitem_comment, ETC) When I add the additional columns (after the distinct statement) it doesn't work due to the type of data.

"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"

Is there any way to pull multiple columns, based only on distinct for one column?

I hope I'm explaining this correctly.

Thanks.

Rwj6001

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 16:24:15

You need to include the other columns in the SELECT list. If you post some sample code and expected output someone here can help with the query. Also, I see you are just concatenating values for your SQL string. You should be using Parameterized Queries to avoid SQL Injection attacks. Google with the keywords for more info.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-22 : 22:12:16

Obviously, the view that I'm pulling the information from is workitem_cost_view, the columns that I need are workitem_number, workitem_title, company_contact, workitem_comment. The workitem_number column usually has a few duplicates in that I really only need to pull the first one, which is why I'm trying to use the distinct statement to only pull the first one.

However, as I said previously when I try to add the additional columns to the statement below, I get an error that the distinct statement will not work with data types text, ntext, ETC. After testing it further I found out that the workitem_comment is the one causing that particular error.

So, how do I structure the statement to just use distinct for the one column and not the rest of them? Or is this a case where I need to use multiple statements?

I appreciate your comments about SQL injection attacks and will look into, however, this particular site is not open to the public so its not a huge concern.

SQL Statement with one column
"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"

SQL Statement with multiple columns
"SELECT DISTINCT workitem_number, workitem_title, company_contact, workitem_comment from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"

Thanks!

Rwj6001

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 23:29:28

can you post some sample data and expected output?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-23 : 11:45:07

Sample data in DB
workitem_number - workitem_title company_contact workitem_comment
11027 - problem1 - Bill - commentp1
11028 - problem2 - Tom - commentp2
11029 - problem3 - Ann - commentp3
11027 - problem1 - Bill - Commentp1-2
11027 - problem1 - Bill - commentp1-3
11028 - problem2 - Tom - commentp2-2
11029 - problem3 - Ann - commentp3-2
11028 - problem2 - Tom - commentp2-3
11030 - problem45 - Bob - commentp4

Expected output
11027 - problem1 - Bill - commentp1
11028 - problem2 - Tom - commentp2
11029 - problem3 - Ann - commentp3
11030 - problem45 - Bob - commentp4

Thanks!

Rwj6001

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-23 : 11:50:53

Instead of the DISTINCT clause, use the GROUP BY clause:

SELECT workitem_number, workitem_title, company_contact, min(workitem_comment) AS workitem_comment
FROM Yourtable
GROUP BY workitem_number, workitem_title, company_contact

SQL Server Helper
http://www.sql-server-helper.com

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-23 : 13:05:27

Won't that still return ALL the entries? I only want the first one of each problem number returned, as in my output example above.

Thanks!

Rwj6001

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-23 : 13:18:31

It won't return ALL the entries but should return your sample output. Have you tried it already?

SQL Server Helper
http://www.sql-server-helper.com

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-24 : 09:03:42

I tried the string suggested by sshelper and now get the following error;

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Thanks!

Rwj6001

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-24 : 15:25:20

I think I see what you were trying to do with that statement sshelper, keying on the workitem_comments column to return the minimum values, but I think perhaps I mislead you with my sample data. The last column of my sample data is random text comments, in no particular order or sequence containing no numerical data.

Let me try again.

Sample data
workitem_number - workitem_title - company_contact - workitem_comment
11027 - problem1 - Bill - comments about prob1
11028 - problem2 - Tom - comments about prob2
11029 - problem3 - Ann - comments about prob3
11027 - problem1 - Bill - additional comments about prob1
11027 - problem1 - Bill - more additional comments about prob1
11028 - problem2 - Tom - additional comments about prob2
11029 - problem3 - Ann - additional comments about prob3
11028 - problem2 - Tom - more additional comments about prob2
11030 - problem45 - Bob - Comments about prob45

Expected output
11027 - problem1 - Bill - commentp1
11028 - problem2 - Tom - commentp2
11029 - problem3 - Ann - commentp3
11030 - problem45 - Bob - commentp4

Thanks!

Rwj6001

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-24 : 16:24:38

Injection is only one of the many reasons to use parameters. please read:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

You should never, never, never concatenate data into a sql string like that -- especially dates and string because now you have formatting and delimiting issues to deal with.

Simply always use parameters, and even better, use stored procedures as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-24 : 22:17:09

Jeff,

Thank you for your comments and I understand how serious the issue is. I will be taking further steps to correct this issue.

That said, given the current sample data and expected output, is there anyway I can get the data I need, in the format that I need it?

Thanks!

Rwj6001

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-25 : 08:26:55

Hi Rwj6001 -- OK, as long as you promise! Don't come back here with more concatenated sql code of you'll get an earful from me!

you just need to solve your problem in two steps:

Step1, get the Min(workItem_title) per workItem_number:


select workitem_Number, Min(WorkItem_title) as MinTitle
from workitem
group by workItem_number

(BTW -- that's a really bad naming convention. sorry to pick on you again, but you should never prefix your column names with the table name. It's completely redundant and means related columns in different tables won't have the same column name, which can be confusing.)

Then, from there, you just JOIN that result to your primary select, using it as a filter:


select w.*
from workitem w
inner join
  (the previous sql statement) w2 on w.workitem_number = w2.workitem_number and
                                     w.workitem_title =w2.Mintitle

- Jeff
http://weblogs.sqlteam.com/JeffS

Rwj6001
Starting Member

12 Posts

Posted - 2007-05-25 : 10:18:19

Jeff,

Are you kidding me? If I came back here with more concatenated sql code I'd never hear the end of it from everyone!

I agree 100% on the column naming and would never do that in a DB of my own design, however this one was designed by someone else and I'm forced to work within the confines of that design.

I'll try your solution as soon as I can (I'm out of the office today onsite at a different location)

Thanks again for your input!

Rwj6001

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources