SQL MIN function

[gregwga50]

I have a program that reads a customer file and then uses the customer city to go to a tax file to extract the lowest tax number for each group of records that have the customer city embedded in the tax authority name.

Example:

When customer city = 'MOBILE' and customer state = 'AL' retrieve the lowest tax# from the following group of records in the tax file:

TAX#	TAX AUTHORITY
4900	Mobile County Only
4902	Mobile Bayou La Batre
4906	Mobile Chunchula
4908	Mobile Coden
4912	Mobile Grand Bay
4913	Mobile Irvington
4915	Mobile Mobile
4917	Mobile Prichard
4918	Mobile Saint Elmo
4919	Mobile Saraland
4921	Mobile Semmes
4922	Mobile Theodore
4994	Mobile Mobile PJ

The result should yield 4900.

I've tried doing this using the MIN SQL function but cannot get it to work. I wonder if it would be easier just to dump the SQL result set into a MODS or array, sort the array/MODS and retrieve the 1st entry. Any ideas how to do this with SQL?

[jj_dahlheimer]

Min should work just fine, what are the data types and what code do you have with what results you are seeing? Below code returns 4900.

select
min(tax#)
from
(
   values(4900 ,'Mobile County Only'),
   (4902 ,'Mobile Bayou La Batre'),
   (4906 ,'Mobile Chunchula'),
   (4908 ,'Mobile Coden'),
   (4912 ,'Mobile Grand Bay'),
   (4913 ,'Mobile Irvington'),
   (4915 ,'Mobile Mobile'),
   (4917 ,'Mobile Prichard'),
   (4918 ,'Mobile Saint Elmo'),
   (4919 ,'Mobile Saraland'),
   (4921 ,'Mobile Semmes'),
   (4922 ,'Mobile Theodore'),
   (4994,'Mobile Mobile PJ')
)
as temp(tax#,taxauth)

[gregwga50]

Min should work just fine, what are the data types and what code do you have with what results you are seeing? Below code returns 4900.

select
min(tax#)
from
(
values(4900 ,'Mobile County Only'),
(4902 ,'Mobile Bayou La Batre'),
(4906 ,'Mobile Chunchula'),
(4908 ,'Mobile Coden'),
(4912 ,'Mobile Grand Bay'),
(4913 ,'Mobile Irvington'),
(4915 ,'Mobile Mobile'),
(4917 ,'Mobile Prichard'),
(4918 ,'Mobile Saint Elmo'),
(4919 ,'Mobile Saraland'),
(4921 ,'Mobile Semmes'),
(4922 ,'Mobile Theodore'),
(4994,'Mobile Mobile PJ')
)
as temp(tax#,taxauth)