Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    30

    Unanswered: Help needed with complex query

    Hi,

    I have a sql table, over 30 milion recs, with the following fields:
    (id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
    I have also sql table, with the following fields: (item varchar(10), rate int)

    I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
    The output need to be ordered as:
    - Get id1 & id2 with the bigest rate
    - output all the recs for the id1 & 2, ordered by rate (sub order)
    eg
    Main table:
    id1 id2 itemsCollection myText
    1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
    1 1 'a,b' 'count-11-a-B - max = 15'
    1 1 '' 'count-11'
    1 1 'a,c' 'count-11-a,C - max = 20'
    2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
    2 8 'c,d' 'count-28-C-d - max = 20'
    2 8 'd' 'count-28-D - max = 5'
    3 2 'a,d' 'count-32-A-d - max = 10'
    3 2 '' 'count-32'

    Rates table:
    item rate
    a 10
    b 15
    c 20
    d 5
    '' 0

    RequestedOutput:
    itemsCollection item rate id1 id2 myText
    a,c c 20 1 1 count-11-a,C - max = 20
    a,b b 15 1 1 count-11-a,B - max = 15 additional txy
    a,b b 15 1 1 count-11-a,B - max = 15
    0 1 1 count-11
    c,d c 20 2 8 count-28-C-d - max = 20 additional txt
    c,d c 20 2 8 count-28-C-d - max = 20
    d d 5 2 8 count-28-D - max = 5
    a,d a 10 3 2 count-32-A-d - max = 10
    0 3 2 count-32
    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need an application program for that logic

    you can sort the results of a join (and in your case the join will be monstrously inefficient, because of the comma-separated list of ids) by descending order of rate, but you can not "take a side trip" and sort all the a/b rows up under the a/b row with the largest rate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    30
    r937, thanks for reply.
    No app available - I'm trying to do it using cursor & temp tables, because

    Thanks anyway

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, then transact-sql is your application programming language

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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