# Thread: Showing a number with a GROUP BY

1. Registered User
Join Date
Jul 2003
Posts
55

## Unanswered: Showing a number with a GROUP BY

Hi All,
Ramadan Kareem for all muslims, and cheers for others!

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. Registered User
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

3. Window Washer
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...

4. Registered User
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.

^^

5. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

7. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
Join Date
Oct 2003
Posts
706
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.

10. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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.

#### Posting Permissions

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