Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: 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,

    Guyang

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1.
    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/

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    Last edited by blindman; 09-13-03 at 20:33.

  4. #4
    Join Date
    May 2002
    Posts
    55
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman
    Last edited by blindman; 09-14-03 at 01:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •