<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Shreyes,<br>
<br>
to select only the first row of a group of rows ordered by the
values of a certain column, I would use DISTINCT ON. For your
example table this would look like this:<br>
<br>
select distinct on (plot_id) plot_id, area from intersects_result
order by plot_id, area desc;<br>
<br>
Hope that helps,<br>
<br>
Birgit.<br>
<br>
<br>
<br>
Am 03.05.2011 14:11, schrieb shreyes shiv:
<blockquote
cite="mid:BANLkTimDEpGjV-D11E7JbLq9KMbS6KD8gw@mail.gmail.com"
type="cite">Dear all,
<div><br>
</div>
<div>I have loaded two shape files into postgis database.</div>
<div>I am using the ST_Intersects(geom of shape file1, geom of
shape file2) command to see the features that interesects.</div>
<div>Now there are redundant entries created in the table.</div>
<div>I want to pick the unique tuple where the attribute "area"
has the maximum value.</div>
<div>for ex. the following is a outcome of doing the intersects
operation on the two tables.</div>
<div><br>
</div>
<div>plot_id area </div>
<div>--------------------------------</div>
<div>S01BAT009 23</div>
<div>S01BAT009 20</div>
<div>S01BAT009 19</div>
<div>S01BAA001 10</div>
<div>S01BAA001 12</div>
<div>S01BAA001 30</div>
<div><br>
</div>
<div>I want to select </div>
<div><br>
</div>
<div>plot_id area</div>
<div>-------------------------------</div>
<div>S01BAT009 23 </div>
<div>S01BAA001 30 </div>
<div><br>
</div>
<div>from the above table. </div>
<div><br>
</div>
<div>How do i write the query please help.</div>
<div><br>
</div>
<div>thank you</div>
<div><br>
</div>
<div>-- <br>
shreyes shiv<br>
email: <a moz-do-not-send="true"
href="mailto:shivshreyes@gmail.com">shivshreyes@gmail.com</a><br>
phone: 9557975780<br>
IIRS(Indian Institute of Remote Sensing)<br>
No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India<br>
</div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>