selecting a field in every tuple & making it bold

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting a field in every tuple & making it bold

Author

Topic

trickedicky
Starting Member

1 Post

Posted - 2007-11-19 : 09:49:16

Hi,

i have a table, that i am trying to compare values. I want to highlight the cheapest field's contents (price) for each tuple. The field itself could be different for each field depending on its price. the table i have is below;

Pos | AlbumTitle | Artist |Virgin | Woolworths | hmv
-----+---------------+--------------+-------+-------------+---------
1 | Confessions | Usher | 2.99 | 9.99 | 13.79
2 | Back Home | Westlife | 2.00 | 5.99 | 3.19
3 | Spirit | Leona | 5.99 | 2.99 | 13.99
4 | Trust Me |Craig David| 7.99 | 11.99 | 6.63
5 | Sawdust | Killers | 22.99 | 10.99 | 8.39
6 | Greatest Hits |Spice Girls| 12.99 | 0.99 | 13.79

im not sure if this would be a good format for the table, i cant figure out how to find out which field is the lowest value and how at all i would be able to make the value itself bold or stand out somehow.

Any help/advice would be welcome!!!
Rich

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 09:53:01

This is not something that you do in SQL, you do it on your report or webpage or wherever you are displaying the data. there is now way to make a column "bold" in SQL Server, it is a database, not a presentation tool.

- Jeff
http://weblogs.sqlteam.com/JeffS

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-19 : 10:01:16

To make it bold is a presentation issue, which does not belong to here.

Use min() function to return the lowest value in a given column.

When you said "for each tuple", did you mean to identify the 2.99 value for Pos 1?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-19 : 10:16:37

No it is not a good format.
Price data should be in a seperate table.

That being said here is a way to get the lowest price per tuple.

declare @A table (pos int, AlbumTitle varchar(100), Artist varchar(100), Virgin float, Woolworths float, hmv float)
insert into @A
select 1 , 'Confessions' , 'Usher' , 2.99 , 9.99 , 13.79
union all
select 2 , 'Back Home' , 'Westlife' , 2.00 , 5.99 , 3.19
union all
select 3 , 'Spirit' , 'Leona' , 5.99 , 2.99 , 13.99
union all
select 4 , 'Trust Me' ,'Craig David', 7.99 , 11.99 , 6.63
union all
select 5 , 'Sawdust' , 'Killers' , 22.99 , 10.99 , 8.39
union all
select 6 , 'Greatest Hits' ,'Spice Girls', 12.99 , 0.99 , 13.79

select * from @A
select
pos,
AlbumTitle,
Artist,
case
WHEN Virgin < WoolWorths AND Virgin < hmv then Virgin
WHEN Woolworths < hmv then Woolworths
Else hmv
END as LowestPrice,
case
WHEN Virgin < WoolWorths AND Virgin < hmv then 'Virgin'
WHEN Woolworths < hmv then 'Woolworths'
Else 'hmv'
END as LowestLocation
from @A

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources