Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Get some values from a group

    Hello,

    here's my problem. There's table t with:
    Code:
    year, id, price
    2000  1   100
    2000  1   200
    2000  2   100
    2000  3   500
    2000  4   100
    2001  1   100
    2001  2   300
    ...
    I need a way, to get those prices, so I have e.g. 3 Groups for every year with the same number of elements in it. Here's the bakground:
    My employer wants a report for the sold objects in each year classified by upper, middle and lower price level. The biggest problem is, that I cannot define prices manually. What I have to do, is to order prices in each group (e.g. year), combine that with row_number(), devide row_count by 3, do a loop with step (row_number / 3) and get price at that position.
    But getting those prices gives me the willies. I just discoverd the "MODEL" Clause. Do you think, that can help me solve the problem?
    Maybe you have some tips.
    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, anything with "do a loop" in the requirements is not going to get a lot of responses on this site

    we normally suggest that you restate your requirements in terms of what data you want, rather than how to get it (especially if how to get it involves cursors)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    nevermind, I think ntile is what i'm looking for ;-)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The statistically correct way to do what you've described using sets is:
    Code:
    CREATE TABLE #p (
       yearID	INT
    ,  ID		INT
    ,  price	MONEY
       )
    
    INSERT INTO #p (
       yearID, ID, price
       ) SELECT            2000, 1, 100
          UNION ALL SELECT 2000, 1, 200
          UNION ALL SELECT 2000, 2, 100
          UNION ALL SELECT 2000, 3, 500
          UNION ALL SELECT 2000, 4, 100
          UNION ALL SELECT 2001, 1, 100
          UNION ALL SELECT 2001, 2, 300
    
    SELECT a.yearID, a.ID, a.price
    ,  1 + Convert( INT, (SELECT Count(*)
          FROM #p AS b
          WHERE  b.price < a.price)
    /  (SELECT Count(*) / 3.0 FROM #p AS c))
       FROM #p AS a
    
    DROP TABLE #p
    Note that the conversion back to INT (forcing truncation) is very important from a statistical perspective.

    -PatP

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    Thank you Pat! I could make it with ntile

    Code:
    select year,pos,min(price) from
    (
       select
          ntile(4) over(partition by year order by price) as pos,
          year,
          price
       from
          (select distinct id,year,price from #t ) as a
    )as b
    group by pos,year
    This works fine, because I dont need to know anything about the data before. Anyway, this query doesn't look optimal. Is there a way to reduce selects?
    Thx!

Posting Permissions

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