SQL: Alternative to "First" function?

Question

I'm trying to write a query I don't want to have Cartesian products on. I was going to use the First function, because some Type_Codes have multiple descriptions, and I don't want to multiply my dollars.

Select
 Sum(A.Dollar) as Dollars,
 A.Type_Code,
 First(B.Type_Description) as FstTypeDescr
From
 Totals A,
 TypDesc B
Where
 A.Type_Code = B.Type_Code
Group by A.Type_Code

I just want to grab ANY of the descriptions for a given code (I don't really care which one). I get the following error when trying to use FIRST:

[IBM][CLI Driver][DB2/AIX64] SQL0440N  No authorized routine named "FIRST" of type "FUNCTION"

Is there another way to do this?

Answer 1

Instead of First(), use MIN().

Answer 2

I forget the actual name of this feature, but you can make it so you actually join to a subquery. in that subquery, you can do like oxbow_lakes suggests and use "top 1"

something like:

select * from table1
 inner join (select top 1 from table2) t2 on t2.id = table1.id

Answer 3

first() is not SQL standard. I forget what database product it works in, but it's not in most SQL engines. As Recursive points out, min() accomplishes the same thing for your purposes here, the difference being that depending on indexes and other components of the query, it may require a search of many records to find the minimum value, when in your case -- and many of my own -- all you really want is ANY match. I don't know any standard SQL way to ask that question. SQL appears to have been designed by mathematicians seeking a rigorous application of set theory, rather than practical computer geeks seeking to solve real-world problems as quickly and efficiently as possible.