Return One Row Per ID

[Cobby1812]

Help I am stuck....bet you havent heard that before...anyway to the issue.

I am trying to retrieve a row per case out of my database.

Basicially I am trying to find the first code for every caseheader_rsn in a table....

select CASEHEADER.CASEHEADER_RSN,
CASEHISTORY.CODE,
CASEHISTORY.SYSTEMDATE
from caseheader
inner join casehistory
on caseheader.caseheader_rsn = casehistory.caseheader_rsn

inner join caseevent
on casehistory.caseheader_rsn = caseevent.caseheader_rsn

WHERE CODE IN ('2010', '2004', '2014', '2017', '1458')
AND CASEHEADER.CASEHEADER_RSN = 1777

THE RESULTS SHOW AS
1777 2014 2009-07-30 11:37:10.763
1777 2010 2010-11-02 14:45:31.973
1777 1458 2010-12-22 12:35:26.357

In the query above I have added a where clause to a specific CaseHeader_rsn which shows three rows for one caseheader_rsn. I want to return the the first one, not just for one caseheader_rsn but for all the caseheader_rsn I have in the table.

Hope this makes sense. What I want to get to is:
1777 2014 2009-07-30 11:37:10.763
1862 2010 2010-11-02 14:45:31.973
1928 1458 2010-12-22 12:35:26.357

and so on.

Help me please.

 

[SQLScholar]

Try this.....


select CASEHEADER.CASEHEADER_RSN,
CASEHISTORY.CODE,
CASEHISTORY.SYSTEMDATE
from caseheader
INNER JOIN
(select caseheader_rsn,
min(CASEHISTORY.SYSTEMDATE) as min_SYSTEMDATE
from casehistory) Early_casehistory
on Early_casehistory.caseheader_rsn = CASEHEADER.CASEHEADER_RSN
inner join casehistory
on caseheader.caseheader_rsn = casehistory.caseheader_rsn
and Early_casehistory.min_SYSTEMDATE = caseheader.SYSTEMDATE




----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[markros]

Take a look at these blog posts explaining how to solve this common problem

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library