title: SQL query to select one of multiple rows from a table
body: <p>I have a table which contains more than one row for a particular value . Here is the table structure:</p>
  
  <pre><code>NAME,NUMBER,STATUS,DESC,START_DATE,END_DATE
  A,3,X,DetailsOfX,13-10-15,13-10-15
  A,2,Y,DetailsOfY,13-10-15,13-10-15
  A,2,Z,DetailsOfZ,13-10-15,13-10-15
  A,1,X,DetailsOfX,12-10-15,12-10-15
  </code></pre>
  
  <p>The output i need is i.e.</p>
  
  <pre><code>A,3,X,DetailsOfX,13-10-15,13-10-15
  A,2,Y,DetailsOfY-DetailsofZ,13-10-15,13-10-15
  A,1,X,DetailsOfX,12-10-15,12-10-15
  </code></pre>
  
  <p>So basically i want to select one of two or more rows from a table with data from columns from both the rows (in bold above). The query below i tried using JOIN returns 4 rows. </p>
  
  <pre><code>SELECT A.NAME,A.NUMBER,B.STATUS,A.DESC||"-"||B.DESC,A.START_DATE,A.END_DATE
  FROM TABLE A
  JOIN (SELECT NUMBER,STATUS,DESC,START_DATE,END_DATE FROM TABLE WHERE NAME='A') B
  ON A.NAME=B.NAME AND
  A.NUMBER=B.NUMBER
  </code></pre>
  
  <p>Can somebody help me with the query that would work.</p>
  
  <p>Thanks</p>