Select Query - Should be an easy one!!!!!!!!!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Query - Should be an easy one!!!!!!!!!!!

Author

Topic

cobby1812
Starting Member

16 Posts

Posted - 2012-01-24 : 12:25:18

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 event in a table....but no matter what i do I always end showing three rows.

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

sO I WANT THE QUERY TO RETURN

1777 2014, 2009-07-30 11:37:10.763

hOW ON EARTH DO i DO THAT

Opps caps locks on now.........

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-24 : 12:31:42

SELECT TOP 1....

ORDER BY <CONDITION>

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-24 : 12:32:24

SELECT TOP (1) .... ORDER BY SystemDate ??

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-24 : 12:33:23

like:


BEGIN TRAN

SELECT TOP 1
	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
ORDER BY
	CASEHISTORY.SYSTEMDATE ASC

ROLLBACK

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cobby1812
Starting Member

16 Posts

Posted - 2012-01-24 : 12:39:31

Awesome of the most awesomness...thank you kindly.

cobby1812
Starting Member

16 Posts

Posted - 2012-01-24 : 12:48:18

Sorry...what if I want to return multiple rows.................

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-24 : 13:03:04

Multiple rows as in?????

--
Gail Shaw
SQL Server MVP

cobby1812
Starting Member

16 Posts

Posted - 2012-01-25 : 03:25:56

As in the select Top 1 will return one row. What I want to do is only select the first code from each instance of a caseheader_rsn. ie.

1234, 2014, 01012012
2345, 2010, 01012012

etc

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 04:16:04

use a ranking function like ROW_NUMBER()


SELECT
	 dt.[CASEHEADER_RSN]
	 , dt.[CODE]
	 , dt.[SYSTEMDATE]
FROM
	(
	SELECT
		cHead.[CASEHEADER_RSN] AS [CASEHEADER_RSN]
		, cHist.[CODE] AS [CODE]
		, cHist.[SYSTEMDATE] AS [SYSTEMDATE]
		, ROW_NUMBER() OVER (PARTITION BY cHead.[CASEHEADER_RSN] ORDER BY cHist.[SYSTEMDATE] ASC) AS [rnk]
	FROM
		caseheader AS cHead
		JOIN casehistory AS cHist ON cHead.[caseheader_rsn] = cHist.[caseheader_rsn]
		JOIN caseevent AS cEvent ON cHist.[caseheader_rsn] = cEvent.[caseheader_rsn]
	WHERE
		cHist.[CODE] IN ('2010', '2004', '2014', '2017', '1458')
		-- AND cHead.[CASEHEADER_RSN] = 1777
	)
	AS dt 
WHERE
	dt.[rnk] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources