SQL: an alternative to the "first" function?

Question

I am trying to write a query on which I do not want to have Cartesian products. I was going to use the First function, because some Type_Codes have several descriptions, and I do not 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 get ANY of the descriptions for this code (I don't care which one). When I try to use FIRST, I get the following error:

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

Is there any other way to do this?

Answer 1

Instead of First() use MIN() .

Answer 2

first () is not an SQL standard. I forgot which database product it works, but not in most SQL engines. As Recursive notes, min () does the same for your purposes here, with the difference being that, depending on the indexes and other components of the query, you might need to search many records to find the minimum value, when in your case - - and many from my own - all you really want is ANY match. I do not know the standard SQL queries to ask this question. SQL, apparently, was designed by mathematicians seeking a rigorous application of set theory, and not practical computer geeks who seek to solve real problems as quickly and efficiently as possible.

Answer 3

I forgot the actual name of this function, but you can make it so that you really join the subquery. in this subquery you can do as oxbow_lakes suggests and use "top 1"

sort of:

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