0

What I'm trying to do is concatenate two fields of a table, sort them by the descending length of the concatenated fields and get the top result...

Here's the SQL I'm using to try and achieve this... 

declare @color nvarchar(255);
declare @board_name nvarchar(255);

select top(1)
    @board_name = board_name, 
    @color = base_color + case when shade_color is null then '' else '/' + shade_color end 
from 
    cut_vinyl
order by 
    len(@color) desc;

select @board_name, @color;

So, if I had the following table for cut_vinyl

board_name | base_color | shade_color
=====================================
board0001  | clear      | NULL
board0002  | clear      | blue
board0003  | bronze     | bronze
board0004  | bronze     | green
board0005  | bronze     | blue
board0006  | bronze     | NULL

Then I would expect @board_name to be board0003 and @color to be bronze/bronze, yet what I seem to be getting instead is a result like @board_name = board0001, and @color = clear

Drew Chapin
  • 7,779
  • 5
  • 58
  • 84

1 Answers1

1

You're ordering on a variable:

order by 
    len(@color) desc;

If you're looking for the longest color, use:

order by 
    len(base_color) + len(shade_color) desc;

instead.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Given the table above, the result I expect should only ever return **board0003**. I don't just need the longest `base_color`, I need the longest `base_color + '/' + shade_color`. – Drew Chapin Jul 31 '12 at 07:13
  • That's right if you also order on the length of `shade_color`. I've edited the answer. – Andomar Jul 31 '12 at 07:16