in

ASP.NET Weblogs

Dave Burke - A freelance .NET Developer specializing in Online Communities

A freelance .NET Developer

Using SQL to obtain unique values, a guilty pleasure

I have a list of int IDs of individuals associated with a subject, with the list coming from various sources.  There are most likely duplicates, but I want unique values only.

I considered what type of algorithm I would use.  An array perhaps, sort it and compare.  Quick sort?  Double-linked list?  Binary tree?  (I'm being ridiculous now.  Sorry.)  But then it hit me that all of the user IDs, regardless of their origination, were found in a users SQL table, so after obtaining a team_ids string which looked something like '3,44,313,313,12', I turned to SQL to be my brains:

 sql = "select distinct id from users where id in (" + team_ids + ") order by id";
 SqlDataReader dr = SQLHelper.ExecuteReader(sql);
 while (dr.Read())...
 
KISS, baby.

Published Apr 28 2004, 04:01 PM by daveburke
Filed under:

Comments

 

Christian Nagel said:

What about using GUIDs for unique identifiers?
Christian
April 28, 2004 4:27 PM
 

Dave Burke said:

Christian, Because the use of int IDs from the user table is so pervasive throughout the schema, my thinking is that the simple int (in the user table an identity value) is preferrable. ?
April 28, 2004 4:29 PM
 

Mischa said:

use of a dictionary object :)

its bound to be quicker :)

ht(id) = " "
ht(id) = " "
ht(id) = " "

April 28, 2004 5:13 PM
 

AT said:

Welcome to insecure world ;o)

SQL Injection attacks waiting for you.
Once you will get team_ids = "9) or (1=1" you will be hacked ;o)
Also it will not work for big input (as there is limited length for SQL string) and it's pretty dam slow.

Preferred is to use String.Split(), ArrayList, sort and iterate comparing with prev element.

The only exception is in case if you need all thouse IDs to read more data from database. This way passing all IDs in one SQL statement offload your database from answering numerous statement by using only one. But SQL string limitation still here.
April 28, 2004 5:38 PM
 

Dave Burke said:

Mischa and AT, Both excellent alternatives. Thanks much! I'll re-evaluate when faced with this again.
April 28, 2004 7:14 PM
 

Brendan Tompkins said:

Eeeew. Running this through SQL seems like using a bulldozer to flatten an anthill.

How about this?

ArrayList al = new ArrayList();
string [] teamIDs = team_ids.Split(new char[]{','});
foreach(string teamID in teamIDs)
{
if(!al.Contains(teamID)) al.Add(teamID);
}

foreach(string uniqueTeamID in al)
{
// Build your comma list here if you need it.
}

-Brendan
April 29, 2004 9:45 AM
 

Dave Burke said:

Brenda, you da man! Thank you for this non-SQL better way.
April 29, 2004 10:02 AM
 

Jon Galloway said:

And there are several ways to select from a comma separated value list in a stored procedure without using dynamic sql, so the sql injection thing goes away:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20675

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33866
April 30, 2004 3:13 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
Terms of Use