Databases
StackOverflow2013
Postsdbo
POTSQL grouping/select help
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTSQL grouping/select help
    text
    copied!<p>Hi all wonder if someone can lend a hand; i've got this tsql script (shown below) that is currently returning data based on the owner id, if the record is active and if the record created date is less than todays date. I am then grouping the data together. What i want to achieve is return the most recent record per company.</p> <p>Currently the data i return is this:</p> <pre><code>COMPANY A JOE BLOGS NULL 10088 Green NULL NULL 21/07/2007 16:57 Phone Call COMPANY B JOE BLOGS NULL 10059 Green NULL NULL 20/07/2007 14:57 Phone Call COMPANY B JOE BLOGS NULL 10059 Green NULL NULL 18/07/2006 09:47 E-mail COMPANY B JOE BLOGS NULL 10059 Green NULL NULL 19/07/2006 13:19 E-mail COMAPANY C JOE BLOGS NULL 10866 Green NULL NULL 17/08/2007 12:57 Phone Call COMAPANY C JOE BLOGS NULL 10866 Green NULL NULL 13/08/2007 10:59 E-mail COMAPANY C JOE BLOGS NULL 10866 Green NULL NULL 15/08/2007 14:57 E-mail </code></pre> <p>This is how i want the data to return:</p> <pre><code>COMPANY A JOE BLOGS NULL 10088 Green NULL NULL 21/07/2007 16:57 Phone Call COMPANY B JOE BLOGS NULL 10059 Green NULL NULL 20/07/2007 14:57 Phone Call COMAPANY C JOE BLOGS NULL 10866 Green NULL NULL 17/08/2007 12:57 Phone Call </code></pre> <p>Could someone, point me in the right direction please?</p> <pre><code>SELECT fa.name, fa.owneridname, fa.new_technicalaccountmanageridname, fa.new_customerid, fa.new_riskstatusname, fa.new_numberofopencases, fa.new_numberofurgentopencases, fap.actualend, fap.activitytypecodename, fap.createdby, fap.createdbyname FROM FilteredAccount fa INNER JOIN FilteredActivityPointer fap ON fa.accountid = fap.regardingobjectid WHERE fa.statecodename = 'Active' AND fap.ownerid = '0F995BDC' AND fap.createdon &lt; getdate() GROUP BY fa.name, fa.owneridname, fa.new_technicalaccountmanageridname, fa.new_customerid, fa.new_riskstatusname, fa.new_numberofopencases, fa.new_numberofurgentopencases, fap.actualend, fap.activitytypecodename, fap.createdby, fap.createdbyname </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload