December 30, 2005 at 2:39 pm
I've got a table that contains information on data types, called tblDataType. It contains fields ID, Name, and a Length field. ID is a smallint which increments automatically and is the primary key. Name is varchar of size 50, and Length is tinyint.
When a user creates a new data type, it can have a similar name, but with a different length. Simple example :
When I retrieve this data, I am trying to bind the Name and Length fields to a list. I'd like to do this with a single SELECT statement; however, I do not want duplicate Name fields in the list.
Example:
SELECT DISTINCT Name, Length FROM tblDataType
I realized that because I am selecting both Name and Length fields, I would not eliminate duplicate names, due to the Length fields being DISTINCT.
I am reluctant to copy the table and delete duplicate fields because I may modify the table in the future such that the ID is of type int or bigint, allowing for a much larger table size.
Is there a way I can narrow the scope of DISTINCT to a subset of the fields I am requesting?
Any ideas?
December 30, 2005 at 3:05 pm
Select Name, Min(Length) As Length
From tblDataType
Group By Name
December 30, 2005 at 3:25 pm
Min or Max could be used here. This is all depending on the business rules in this case. You might have to actually delete all the duplicates to make this work in the long run and enforce uniqueness as a rule so that you eliminate that problem for good.
January 1, 2006 at 9:09 pm
Thanks for replying over New Year holiday! Min should work. Rows with duplicate Name / Length combinations will be monitored and deleted.
The data types are representing data types created by administrators; however, native data types need to be available, as well. For instance, if I was storing information about MS-SQL Server 2000 data types for this project, I could have multiple rows with Name="char" but each be unique if the Length field varied. char[50] and char[10] are unique structures, but they do share the rules of the "char" data type.
Then an administrator can create a more complex object, such as Name, which consists of char[50] for FName, char[50] for LName, char[10] for Suffix. There's better ways to execute this example, but it's simple enough to identify the purpose of allowing duplicate names.
Again, thanks for the input!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply