sql query
from this table i have to take those records that have common gridcode and then from these records i have to store the records having maximum area in another table...
gridcode state area
13013 rajasthan 50000
13013 gujarat 45000
13014 rajasthan 25000
13014 gujarat 25000
Software/Hardware used:
Software/Hardware used:
ASKED:
April 30, 2008 5:50 AM
UPDATED:
April 30, 2008 12:10 PM
Answer Wiki:
A join will work out for you.
<pre>INSERT INTO AnotherTable
SELECT a.GridCode, a.State, a.Area
FROM Table
JOIN (SELECT GridCode, max(Area) as Area
FROM Table
GROUP BY GridCode) b ON a.GridCode = b.GridCode AND a.Area = b.Area</pre>
If each GridCode contains only one state (I assumed that this was not the case).
<pre>INSERT INTO AnotherTable
SELECT GridCode, State, max(Area)
FROM Table
GROUP BY GridCode, State</pre>
The first example I gave is the more precise example.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.