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
) ON [PRIMARY]
) ON [PRIMARY]
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) --, ???
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?
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.
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.
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.
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.