[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL query help needed: Getting next highest record

Posted on 2009-04-27
Medium Priority
Last Modified: 2012-05-06
I have a table with data as follows (simplified version):
   UID      AssetNo.   MoveDate                      From                To
312513      149551      19/12/2008 12:20:00      1621             1305
283660      149551      29/06/2008 00:52:00      1442             1621
283029      149551      23/06/2008 16:32:00      40                     2093175289
283032      149551      23/06/2008 16:32:00      2093175289      1442
A query on this table returns just the first of the rows shown above.  The To field will always be 1305.  From that query how can I requery the table to give me the MoveDate and From fields in the 2nd row?  The 2nd row will always be the next record chronologically.  But although the records will be in this order in the table they may or may not be the first two records.

Question by:looper8
  • 2
LVL 16

Expert Comment

ID: 24242825
You will need to build it out with the correct table logic but this will work
-- this will select the highest number that is NOT the first number
Select top 1 * From table A
Where UID NOT IN (
 -- this will select the highest number
 Select Top 1 UID from Table A 

Open in new window


Expert Comment

ID: 24242856
Could I get some clarification.

Your query is only showing the first row?

You'd like to query the table to give you the MoveDate and From fields in the second row?

If that is all, the query is:

SELECT movedate, from
FROM table
WHERE UID = 283660;

If you are trying to get a piece of code that finds the 'next' record relative to the record you just pulled (cycling through the table), you will need to create a cursor in a PL/SQL format.

I think answering your question will be simpler if you could include the query code you are using.

Author Comment

ID: 24243244
Thanks guys ...
brad2575: The first row may be the latest row added and so have the highest UID, but it might not be.
laneybeal: What I've given is just sample data so the UID number could be anything.

I've attached the full code below.  

At --1 data is inserted into a temp table, this is like the data I showed before.
This is to do with stock movements. When an item is moved from anywhere to a certain location (the 1305 I mentioned before, although it's a few other places too) then this bit of code puts a record of that event into the temp table. Now I've been asked to identify not just the movement that put it in that place but also the previous movement.
declare @EMail varchar(100)
--declare @strEMail varchar(400)
declare @Spacer char(2)
set @Spacer = ' '
declare @SQLStart varchar(200)
set @SQLStart = convert(char(8), 'Contract')+ @Spacer + convert(char(40),'Part Code / Description') + @Spacer + convert(char(15), 'Serial No.') + @Spacer + convert(char(10), 'Asset No.') + @Spacer + convert(char(50),'Moved To / From / By')
declare @SQLPart varchar(250)
declare @strSQL nvarchar(4000)
set @strSQL = ''
--set @strEMail = 'steve.dawson@telent.com, lloyd.hutchinson@telent.com, samantha.peters@telent.com, sharon.backhouse@telent.com, alhaji.carew@telent.com, paul.instone@telent.com, krishna.pentayya@telent.com, michael.hayward@telent.com'
--set @strEmail = 'peter.fender@telent.com'
create table #tmpResults
	fldAssetUID int,
	fldFromLocationUID int,
	fldToLocationUID int,
	fldName char(25)
-- 1
insert #tmpResults
select AM.fldAssetUID, fldFromLocationUID, fldTolocationUID, left(rtrim(fldSurname) + ', ' + rtrim(fldFirstName),25)
from tblAssetMovement AM
inner join tblAssets A
on A.fldAssetUID = AM.fldAssetUID
inner join tblContract C
on A.fldContractUID = C.fldContractUID
inner join tblStaff S
on AM.fldUserUID = S.fldStaffUID
where fldTolocationTypeUID = 1				-- Stores
and fldToLocationUID in (1305, 3146, 3147, 3148, 3149)	-- NOSC - Lost, NOSC - Duplicate, NOSC - Cannabalised, NOSC - Project Services, NOSC - Administrative
and fldMovementDate between getdate() - 132 and getdate()	
and C.fldContractGroupUID = 1				-- LUL
declare Results cursor						
select distinct convert(char(8), fldContractCode) + @Spacer + convert(char(40),fldPartCode) + @Spacer + convert(char(15), fldSerialNo) +@Spacer + convert(char(10), isnull(fldLULAssetNo,'')) + @Spacer + convert(char(50), 'From: ' + (SELECT fldStoresLocation FROM tblStoresLocation WHERE tmpResults.fldFromLocationUID = tblStoresLocation.fldStoresLocationUID) + @Spacer + 'To: ' + (SELECT fldStoresLocation FROM tblStoresLocation WHERE tmpResults.fldToLocationUID = tblStoresLocation.fldStoresLocationUID)) + char(13) + char(10) + convert(char(8), '') + @Spacer + convert(char(40),fldPart) + @Spacer + convert(char(15), '') +@Spacer + convert(char(10), '') + @Spacer + convert(char(25),fldName) + char(13) + char(10)
from #tmpResults tmpResults
inner join tblAssets
on tblAssets.fldAssetUID = tmpResults.fldAssetUID
inner join tblPart
on tblAssets.fldPartUID = tblPart.fldPartUID
inner join tblContract C
on tblAssets.fldContractUID = C.fldContractUID
order by 1
declare @CurrentContract varchar(10)
declare @PreviousContract varchar(10)
declare @CSM varchar(35)
declare @Subject varchar(35)
set @CurrentContract = ''
set @PreviousContract = ''
set @Subject = 'Parts Moved to NOSC: '
open Results
fetch next from results into @SQLPart
while @@fetch_Status = 0 
	set @CurrentContract = LEFT(@SQLPart, 6)	
	--print @CurrentContract + ', ' + @PreviousContract
	if @strSQL = ''
		set @strSQL = @SQLPart		
		if @CurrentContract <> @PreviousContract
			select @CSM = (SELECT s.fldEmail FROM tblContract c JOIN tblStaff s ON c.fldContractManagerUID = s.fldStaffUID WHERE c.fldContractCode = @PreviousContract)
			set @Subject = @Subject + @PreviousContract
			print @CSM
			set @strSQL = @SQLStart + char(13) + char(10) + char(13) + char(10) + @strSQL 
			exec sp_send_cdosysmail 'DoNotReply@telent.com', @CSM, @Subject, @strSQL, 'lloyd.hutchinson@telent.com, steve.dawson@telent.com', 'peter.fender@telent.com'		
			--exec sp_send_cdosysmail 'DoNotReply@telent.com', 'peter.fender@telent.com', @Subject, @strSQL, 'peter.fender@telent.com'	
			set @strSQL = ''
			--set @Subject = 'Parts Moved to NOSC: '
		set @strSQL = @strSQL + char(13) + char(10) + @SQLPart			
	set @PreviousContract = @CurrentContract
	fetch next from results into @SQLPart
close results
deallocate results
if @strSQL <> ''
	select @CSM = (SELECT s.fldEmail FROM tblContract c JOIN tblStaff s ON c.fldContractManagerUID = s.fldStaffUID WHERE c.fldContractCode = @PreviousContract)
	set @Subject = @Subject + @CurrentContract
	print @CSM
	set @strSQL = @SQLStart + char(13) + char(10) + char(13) + char(10) + @strSQL 		
	exec sp_send_cdosysmail 'DoNotReply@telent.com', @CSM, @Subject, @strSQL, 'lloyd.hutchinson@telent.com, steve.dawson@telent.com', 'peter.fender@telent.com'
	--exec sp_send_cdosysmail 'DoNotReply@telent.com', 'peter.fender@telent.com', @Subject, @strSQL, 'peter.fender@telent.com'	
--select * from #tmpresults
drop table #tmpResults

Open in new window

LVL 16

Accepted Solution

brad2575 earned 1500 total points
ID: 24244287
You will just have to add order by on the statements in my code.  It does not matter what the UID's are or what order they were created in, it will get the second set of data based off your order by clause.  As long as the UID is a unique field my query will do what you want (select the second row of data and skip the first).

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question