Get second record?[RESOLVED] - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Get second record?[RESOLVED]

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-22 : 11:47:01

I was just wondering if there is anyway to get the second CaseNumber from something like this:

SELECT CaseNumber FROM tblFeePaid WHERE MovedOver = 1 ORDER BY CaseNumber

Thanks!

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 11:59:28

select top 1 CaseNumber from (select top 2 CaseNumber FROM tblFeePaid WHERE MovedOver = 1 ORDER BY CaseNumber) order by CaseNumber desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-22 : 12:06:06

I tried this:


select top 1 CaseNumber 
from (select top 2 CaseNumber FROM tblFeePaid WHERE MovedOver = 1 ORDER BY CaseNumber asc) 
order by CaseNumber asc

But is says this:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'order'.

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 12:11:40

sorry - need a name for the derived table
select top 1 CaseNumber
from (select top 2 CaseNumber FROM tblFeePaid WHERE MovedOver = 1 ORDER BY CaseNumber asc) a
order by CaseNumber asc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-22 : 13:19:28

Thanks for the help, but I need some more. This is what I have been trying to do, but it isn't working. Can anyone see why?


declare @counter int
declare @cases nvarchar(10)
declare @rows int
declare @MyMessage nvarchar(1000)
declare @CRLF char(2)


	set @rows = (select count(casenumber) from tblFeePaid where MovedOver = 1)
	set @counter = 0
	set @cases = (select top 1 CaseNumber from tblFeePaid 
			where MovedOver = 1 order by CaseNumber)
	set @MyMessage = @cases
	set @CRLF = char(10) + char(13) 
		 
	WHILE @counter < @rows
	BEGIN

	  set @rows = @rows + 1
	  set @cases = (select top 1 CaseNumber from tblFeePaid 
			where CaseNumber > @cases and MovedOver = 1 order by CaseNumber)
	  set @MyMessage = @MyMessage + @CRLF + @cases

	END

	if (select count(CaseNumber) from tblFeePaid where MovedOver = 1) = 0
		set @MyMessage = 'There Were No Cases Imported Into The Fee Paid Table Last Night.'
	else
		set @MyMessage = 'Here Are The Cases That Were Transferred Over To The Fee Paid Table Last Night:'
			+ @CRLF  + @CRLF + @MyMessage 
		
	
		exec master..xp_sendmail 
		@recipients = 'brenda@capitaltracer.com', 
		@subject = 'Cases Imported Into The Fee Paid Table',
		@message = @MyMessage

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-22 : 13:38:29

Sometimes I make myself laugh. I had the loop wrong. Here it is now:


WHILE @counter < @rows - 1
	BEGIN

	  set @cases = (select top 1 CaseNumber from tblFeePaid 
			where CaseNumber > @cases and MovedOver = 1 order by CaseNumber)
	  set @MyMessage = @MyMessage + @CRLF + @cases
	  set @counter = @counter + 1
	 
	END

Brenda

If it weren't for you guys, where would I be?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 13:39:21

select @MyMessage = coalesce(@MyMessage + @CRLF, '') + CaseNumber
from tblFeePaid
where MovedOver = 1

if @MyMessage is null
select @MyMessage = 'There Were No Cases Imported Into The Fee Paid Table Last Night.'
else
select @MyMessage = 'Here Are The Cases That Were Transferred Over To The Fee Paid Table Last Night:'
+ @CRLF + @CRLF + @MyMessage
exec master..xp_sendmail
@recipients = 'brenda@capitaltracer.com',
@subject = 'Cases Imported Into The Fee Paid Table',
@message = @MyMessage

You might have to do some stuff for distinct cases but this should be close.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-23 : 11:37:06

Why will this print properly?:


declare @counter int
declare @cases nvarchar(10)
declare @rows int
declare @MyMessage nvarchar(1000)
declare @CRLF char(2)

	set @rows = (select count(casenumber) from tblFeePaid where MovedOver = 1)
	set @counter = 0
	set @cases = (select top 1 CaseNumber from tblFeePaid 
			where MovedOver = 1 order by CaseNumber)
	set @MyMessage = @cases
	set @CRLF = char(10) + char(13)
		 
	WHILE @counter < @rows  - 1
	BEGIN

	  set @cases = (select top 1 CaseNumber from tblFeePaid 
			where CaseNumber > @cases and MovedOver = 1 order by CaseNumber)
	  set @MyMessage =  @MyMessage + @CRLF + @cases
	  set @counter = @counter + 1

	  print @cases

	END
[code]

But this won't print anything:
[code]
declare @counter int
declare @cases nvarchar(10)
declare @rows int
declare @MyMessage nvarchar(1000)
declare @CRLF char(2)

	set @rows = (select count(casenumber) from tblFeePaid where MovedOver = 1)
	set @counter = 0
	set @cases = (select top 1 CaseNumber from tblFeePaid 
			where MovedOver = 1 order by CaseNumber)
	set @MyMessage = @cases
	set @CRLF = char(10) + char(13)
		 
	WHILE @counter < @rows  - 1
	BEGIN

	  set @cases = (select top 1 CaseNumber from tblFeePaid 
			where CaseNumber > @cases and MovedOver = 1 order by CaseNumber)
	  set @MyMessage =  @MyMessage + @CRLF + @cases
	  set @counter = @counter + 1

	 END
print @MyMessage

I can't see why. Thanks for your help!

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-23 : 11:49:08

Nevermind. I was doing the loop wrong. This is what I needed to do:

WHILE @counter < @rows - 2

Thanks though!

Brenda

If it weren't for you guys, where would I be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources