Dispaly mask and select N-M records

Two questions:
1. With SQL, how can I setup display mask for dollar amount :
eg. 100000 display as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang1.
cast(100000 as money)

2.
select top 51 foo
from (
select top 100 foo
from yourtable
order by foo desc
) as derivedtable
order by foo

rudy
http://r937.com/|||Formatting of results is best handled by the user interface (in my humble opinion).

Rudy's method works for a specific case, but the TOP command (very unfortunately) does not accept variables as parameters. You will need to cast your query as a dynamic SQL statement to use it for the general N of M case.

A lot of people come across this issue when trying to produce results that can be "paged" through, such as in a web page. If this is the case for you, then instead of trying to find records N of M, have your interface submit the largest sort-value of the previous recordset as a parameter, and then your procedure can return the first 50 (hard-coded) records that are greater than that value.

blindman|||For question one,

I try the cast(100000 as money), result as:

100000.0000 , any way to output as: $100,000 ?

Thanks for advise.

Guyang|||SELECT '$' + CONVERT (varchar(10), 100000, 1)

If you don't want the decimal values:
SELECT '$' + LEFT(CONVERT(varchar(10), 100000, 1), len(CONVERT (varchar(10), 100000, 1) - 3))

...but I'll say again that it is better to let the interface handle this. I'm thinking that you are coming from the Access world, but Access is a database and an interface wrapped together. SQL Server is purely a database (but much more powerful than MS Access). You should you other tools to communicate with the database and format the output, such as VB applications, MS Access Data Projects, or (yech) Crystal Reports.

blindmansql