Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: max rowcount on fields (please ignore)

    I have an sp that returns data to a client application. I wonder how I'd go about doing the following: I need to implement a maximum amount of rows on a combination of several columns that are part of the resultset.

    fe; Col001, Col002, Col003 are the result, the unique count of Col001 and Col002 are two;

    Col001, Col002, Col003
    1, 'aa', 'someOtherValue'
    1, 'aa', 'someThingElse'
    1, 'aa', 'ratherSillyActually'
    2, 'aa', '...'

    I'd return:
    1, 'aa', 'someOtherValue'
    1, 'aa', 'someThingElse'
    2, 'aa', '...'

    Any idea's?
    Last edited by Kaiowas; 01-21-05 at 06:05. Reason: double post

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Kaiowas
    the unique count of Col001 and Col002 are two;
    Huh?

    So if you added 3,'aa','...'

    Then it becomes 3?

    I'm confused (My natural state)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So you are looking for something like TOP, but for a combination of values rather than for the entire result set? This can be done, but you will need to have some sort order defined. SQL Server does not deal well with arbitrary logic: "pick any three of all the possible results".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Incase there's a row: 3, 'aa', '....' then that would also appear on the output.

    So far, I've setup a subselect joining itself matching the columns that have the maxcount (Col001, Col002). The sub does a top, so in effect this works.

    Code:
    use monkey
    go
    
    set nocount on
    go
    
    create table #temp (Col00X integer identity(1,1), Col001 integer, Col002 varchar(5), Col003 varchar(30))
    go
    insert into #temp (Col001, Col002, Col003) 
               (select 1,     'aa',   'someOtherValue' union select 1, 'aa', 'someThingElse' union select 1, 'aa', 'ratherSillyActually' union select 2, 'aa', '...')
    go
    
    select * from #temp
    
    
    select t1.*
      from #temp t1
     where Col00X in (
        ( select top 2 Col00X 
            from #temp t2
           where t2.Col001 = t1.Col001
          order by t2.Col00X)
     )
    
    go
    drop table #temp
    go

    However, performance is down the drain (somewhere way down under I'd say).

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    and oh... the top 2 is fixed here.. but not in real life...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So you want TOP N, where N is determined at runtime? You can't pass a variable to TOP, so you would have to do your whole statement as dynamic sql .
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    jup! aint it grand?
    and where it took just seconds to haul over 10000 rows, it now takes longer than coffee-and-a-chit-chat-stare-out-the-window-for-an-hour-or-so time.

    So.. I think I'll have to setup an alternate table with prework done already to if I want to keep things going at all...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you can do this relatively quickly return a dynamic number of records for each combination of columns, but you will have to define a sort order that will govern which records are returned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Posts
    492
    You mean write out _all_ of the columns joined in the sort order?

    Did that.. in both the select AND the subselect.. works out nicely (better) when rows are like 7000 returning with 6 (01:33). What worries me is that the IN (..) could be filled with 0 to a million or more values so, I wonder how that performs.
    Last edited by Kaiowas; 01-21-05 at 12:59.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, that is not what I mean.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Posts
    492
    Silly me, should have turned on my HAL9000! Takes a while to start, meanwhile, could you tell me what you did mean?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think what we're trying to say is that TOP has no meaning unless it is in the context of sorting, i.e. ORDER BY something

    in your code example, you're using a identity column for sorting, but that simply numebrs an arbitrary order

    or are you saying "i don't care which two rows, just not more than two"?

    in that case, why not use Col003 (see post #1) as your ORDER BY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Posts
    492
    aha! I care about which records are shown; the ones with the lowest date. So in the 'real' sp I do an order by on the date and identity column, but also include the other columns.

    It just didn't speed up things as much as I was hoping for...

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excuse me for not seeing which of Col001, Col002, Col003 was the date column

    here is a solution (not the only solution, just another solution) to pick the top 2 rows by date from a table which has a composite primary key consisting of columns called, okay, what the heck, Col001 and Col002 --
    Code:
    select t1.Col001
         , t1.Col002
         , t1.Col003
         , t1.DateCol
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.Col001 = t2.Col001
       and t1.Col002 = t2.Col002
       and t1.DateCol <= t2.DateCol     
    group 
        by t1.Col001
         , t1.Col002
         , t1.Col003
         , t1.DateCol
    having count(*) <= 2     
    order 
        by t1.Col001
         , t1.Col002
         , t1.DateCol desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Now that you have finally gotten around to supplying the information I've been asking for...

    Code:
    set nocount on
    
    declare @TopN int
    set	@TopN = 3
    
    create table #KaiwasPost (Col001 int, Col002 char(2), Col003 varchar(20), SortDate datetime)
    insert into #KaiwasPost select 1, 'aa', 'someOtherValue', '1/1/2005'
    insert into #KaiwasPost select 1, 'aa', 'someThingElse', '1/2/2005'
    insert into #KaiwasPost select 1, 'aa', 'ratherSillyActually', '1/3/2005'
    insert into #KaiwasPost select 1, 'aa', 'blahblahblah', '1/4/2005'
    insert into #KaiwasPost select 1, 'bb', 'alkfh', '1/5/2005'
    insert into #KaiwasPost select 1, 'bb', 'wiyrwy', '1/6/2005'
    insert into #KaiwasPost select 1, 'bb', 'oqiyr', '1/7/2005'
    insert into #KaiwasPost select 1, 'bb', 'shoopy-doop', '1/8/2005'
    
    select	A.Col001, A.Col002, A.Col003, A.SortDate, count(*)
    from	#KaiwasPost A
    	inner join #KaiwasPost B
    		on A.Col001 = B.Col001
    		and A.Col002 = B.Col002
    		and A.SortDate >= B.SortDate
    group by A.Col001, A.Col002, A.Col003, A.SortDate
    having count(*) <= @TopN
    order by A.Col001, A.Col002, A.SortDate
    
    drop table #KaiwasPost
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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