locked
What does this statement meaning? RRS feed

  • Question

  • What is the meaning of following sql 2000 code.

    L1 = min(case when LineNum =1 then Data else null)

    Because I do not know the meaning of function min(case...when...then)

    Pls let me know your opinions.

    Thanks

    Thursday, April 29, 2010 5:37 PM

Answers

  • MIN is one of the aggregated functions (others most common are SUM, COUNT, MAX, AVG). Combining it with a CASE statement means that it will only get min for the records with LineNum = 1 and will ignore all other records.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Sedat Salman Thursday, April 29, 2010 5:45 PM
    • Marked as answer by KJian_ Wednesday, May 5, 2010 9:31 AM
    Thursday, April 29, 2010 5:43 PM

All replies

  • MIN is one of the aggregated functions (others most common are SUM, COUNT, MAX, AVG). Combining it with a CASE statement means that it will only get min for the records with LineNum = 1 and will ignore all other records.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Sedat Salman Thursday, April 29, 2010 5:45 PM
    • Marked as answer by KJian_ Wednesday, May 5, 2010 9:31 AM
    Thursday, April 29, 2010 5:43 PM
  • so other data I lose from implementing the above statement?

    for example I have LineNum = 1 and lot of data associated with it. So would it make impact by the data loss?

    Thursday, April 29, 2010 5:50 PM
  • Your question is unclear. By using the above expression in the select statement with Group By some column, you only get one Data value (minimum) out of records that have LineNum = 1. You can use SUM function if you want to sum the data for LineNum = 1 instead of MIN.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 7:25 PM