Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery can be used as a derived table in FORM clause of the T-SQL query. Believe me this is really powerful approach and can bring you out of some difficult situation. I have already previously written some articles on Subquery, you can refer those for getting basic idea about subquery, correlated subquery,CTE etc.

Let us create two tables for demonstration and insert some data in it.

--Create first table

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

--Insert data into first table

INSERT INTO CricketerDetails (Name,Country) VALUES('Sachin Tendulkar','India')

INSERT INTO CricketerDetails (Name,Country) VALUES('Steve Waugh','Australia')

INSERT INTO CricketerDetails (Name,Country) VALUES('Saurav Ganguly','India')

INSERT INTO CricketerDetails (Name,Country) VALUES('Jaisurya','Sri Lanka')

--Create second table

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

--Insert data into second table

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('LG cup',0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-06',10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES('WC-1998',190,1)

After finishing above task, let us see how we can get cricketer name, country and his score with simple JOIN clause.

SELECT C.Name,c.country,MAX(p.Run)

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

GROUP BY C.name,c.country

We have achieved whatever we wanted. If you observed we have to put every column in group by clause which is not included in AGGREGATE function. In this case, those are Name and Country. What if you don’t want to put those two columns in group by clause as this is something messy sometime. Let us see how can achieve the same results with the help of derived subquery as table.

SELECT C.Name,c.country,p.Run

FROM CricketerDetails c

JOIN

(

SELECT Cricketerid,MAX(run) as Run FROM PersonalScore GROUP BY CricketerID

) p

ON c.ID=p.CricketerID

See, we have used subqery in JOIN clause rather than any table and achieved the same results. This technique of relational algebra may prove very powerful sometime in difficult situation.

Reference: Ritesh Shah