Complex histogram question

I have a data series n long and want to show a histogram of the top 10
elements of this list, and show the position of a known record in this
top ten. (This is the easy bit).
Then, if the known record is not in the top 5, I want to display the
top 5 and add the known record at the end, as the 6th, but also
indicate the value of this record on the chart.
E.g I have my top 5 clients from a list of 15. I want to show my
client, Acme, where they are in this list. They turn out to be 15th,
so I want a histogram with the top 5, plus an 'sixth' element which is
Acme, showing it as the 15th in the list...
Below is the sort of chart I am after....
#
#
# 1
# 1 2 3
# 1 2 3 4
# 1 2 3 4 5
# 1 2 3 4 5 15th
##########################
Is this possible'
TIA,
Lisaselect item, value, count(t1.item)+1 rank
from items, items t1
where items.value > t1.value
having count(t1.item) < 5
union
select item, value, count(t1.item)+1 rank
from items, items t1
where item = @.item
There's probably an easier way using Top N.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lisa" <budgenlj@.willis.com> wrote in message
news:9abd1432.0407090612.212701d6@.posting.google.com...
> I have a data series n long and want to show a histogram of the top 10
> elements of this list, and show the position of a known record in this
> top ten. (This is the easy bit).
> Then, if the known record is not in the top 5, I want to display the
> top 5 and add the known record at the end, as the 6th, but also
> indicate the value of this record on the chart.
> E.g I have my top 5 clients from a list of 15. I want to show my
> client, Acme, where they are in this list. They turn out to be 15th,
> so I want a histogram with the top 5, plus an 'sixth' element which is
> Acme, showing it as the 15th in the list...
> Below is the sort of chart I am after....
> #
> #
> # 1
> # 1 2 3
> # 1 2 3 4
> # 1 2 3 4 5
> # 1 2 3 4 5 15th
> ##########################
> Is this possible'
> TIA,
> Lisa