Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    55

    Thumbs up Unanswered: Showing a number with a GROUP BY

    Hi All,
    Ramadan Kareem for all muslims, and cheers for others!
    Please consider the following script



    CREATE TABLE [T] (
    [A] [int] NOT NULL ,
    [B] [int] NOT NULL ,
    [N] [int] NULL ,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
    (
    [A],
    [B]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    INSERT INTO T (A, B, N) VALUES (1, 1, 10)
    INSERT INTO T (A, B, N) VALUES (1, 2, 20)
    INSERT INTO T (A, B, N) VALUES (1, 3, 30)
    INSERT INTO T (A, B, N) VALUES (1, 4, 40)
    INSERT INTO T (A, B, N) VALUES (1, 5, 50)
    INSERT INTO T (A, B, N) VALUES (1, 6, 60)
    INSERT INTO T (A, B, N) VALUES (2, 2, 70)
    INSERT INTO T (A, B, N) VALUES (2, 5, 80)
    INSERT INTO T (A, B, N) VALUES (2, 10, 90)
    INSERT INTO T (A, B, N) VALUES (3, 7, 100)
    INSERT INTO T (A, B, N) VALUES (3, 15, 110)



    SELECT A, MAX(B) --, ???
    FROM T
    GROUP BY A


    1 6 60
    2 10 90
    3 15 110

    What shall I do to show the third column number?!!
    The first two columns are PK, then I need to get the "N" for this key. How?

  2. #2
    Join Date
    Nov 2003
    Posts
    48
    You can do this by using nested query

    Select
    T.A, T.B, T.N
    From T,
    (Select A, Max(B) as Max_B From T Group by A) T1
    where T.A = T1.A
    and T.B = T1.Max_B
    Shianmiin

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's what I call a GREAT post...

    Don't forget the DROPs for clean up...

    And watch out for dups...
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    48
    Thanks.

    DROPs for clean up? The query I posted should not have to clean up anything.

    As for dup, since table T has composite primary key (A, B), there shouldn't have any dup value in this case.

    ^^
    Shianmiin

  5. #5
    Join Date
    Jul 2003
    Posts
    55
    Supose that instead of the PK (A,B) we have another Identity colukn X PK, the query will not work.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    RaedT, can you explain what you mean by "will not work"

    shianmiin's query sure looks like what you asked for

    i don't understand how adding another column would make it stop working
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Posts
    55
    r937, "will not work" subquery will return more than one row.
    In my example I sypposed that the primary key is (A,B) clear?
    supose that there is no primary key on that table, then the query writen earlier "will not work".
    I hope you got me.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, sorry, i don't understand what you're getting at

    i wish i had MSDE or something, so that i could actually run the test myself

    of course the subquery returns more than one row -- it's a derived table (T1) and it is used to join to the original table

    did you run the query on your sample data?

    did you add another column (whether you make it the PK is immaterial) and get different results?


    rudy

  9. #9
    Join Date
    Oct 2003
    Posts
    706

    Thumbs up

    The nested query, as posted, will work. (If the key contained more than one column, they would simply all be listed in the GROUP BY clause.) The keyword DISTINCT can be used in the subquery although a GROUP BY will produce only one group for each unique combination of group-by columns so it is unnecessary.

    Personally, I am a big fan of using a series of queries to do this sort of work instead of one complex query. Tho' the query optimizer in this product is generally a good one, it doesn't take too much to create an unwieldy query... one that's too hard to understand, too hard to debug, too hard to change, and/or inefficient to execute. When you create, instead, a series of queries (using temp tables to hold intermediate results etc), you not only [a] usually do the same thing that the query optimizer decides to do on its own for a complex query; but [b] you reduce the problem into something that's much easier for humans to understand, to change, and to debug. JM2CW.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    99 times out of 100 using temp tables and intermediate processing is demonstrably slower than folding the process into a single query. The speed killer in SQL server is table and and index scans, and these are best avoided by letting the opimizer do its work.

    I'm dealing with a stored procedure now that is over 1500 lines of code, uses five different pre-aggregation tables, a half-dozen temporary tables, and requires ten-fifteen minutes to process. It is a nightmare to debug, and has consumed countless man-hours in attempts to get the correct results.

    My rewrite is under 150 lines of code, uses two temporary tables (which are there only to reduce table-scans, and not for programming convenience) and executes in under three minutes.

    I guess you have to decide whether you are writing code optimized for the end-user or for the convenience of the least skilled developer who may look at it.

    blindman

  11. #11
    Join Date
    Nov 2003
    Posts
    48
    To certain extent, I agree with both blindman's and sundialsvcs's opinions about how to write better queries. I think this is a good discussion and I'd like to share my experience.

    For most of the cases, I would like to rely on the query optimizer to find the best query plan for me. However, due to the complexity of compiling the best query plan in short time, there are chances I have to "help" SQL Server do the query in efficient way, especially the query is very complex, the "help" might include adding query "hints" or decompositing a complex query into several small queries or using temporary tables, etc. The reason why queries become so complicated might be the requirements is already very complicated, but for some cases, it is inherited from bad database schema or bad composed query.

    When I deal with the query optimization, I always look into the nature why the query become so complicated and try to utilize all the ability the database can provide to make the query efficient. Sometimes blindman's idea works, sometimes sundialsvcs's idea works, the answer is not absolute.
    Last edited by shianmiin; 11-23-03 at 16:56.
    Shianmiin

Posting Permissions

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