SQL Server Performance

A SELECT request

Discussion in 'SQL Server 2005 General Developer Questions' started by CanadaDBA, Apr 17, 2008.

  1. CanadaDBA New Member

    Assume we have two tables:
    T1 T2

    F1 F2 Field1 Field2
    A A1 A2 Fred
    A A2 B0 Judy
    B B0 B1 Max
    B B2 B3 Joe
    B B3
    I need to write a SELECT to return:
    - Exact data from Field1 which matches F2; i.e. A2 (from Field1) for A2 (from F2)
    - Or the closest smaller one; i.e. B1 (from Field1) for B2 (from F2)
    Any help is highly appreciated.
  2. martins New Member

    Hi,
    You can try something like this, assuming there will always just be one character at the beginning of the data and the rest is an integer value:
    select F2
    , (
    select top 1
    Field1
    from T2
    where left(T2.Field1,1) = left(T1.F2)
    where convert(int,substring(T2.Field1,2,len(T2.Field1))) <= convert(int,substring(T1.F2,2,len(T1.F2)))
    order by convert(int,substring(T2.Field1,2,len(T2.Field1))) desc
    )
    from T1
    You might have to modify this based on your data...
  3. CanadaDBA New Member

    Thanks for the post. however, here is a clean short resolution.
    SELECT A.F1, A.F2, B.Field1, B.Field2
    FROM @T1 A, @T2 B
    where B.field1 = (select max(t2.field1) from @t2 as t2 where t2.field1 <= A.f2)

Share This Page