SQL query help needed: Getting next highest record

Posted on 2009-04-27
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
    LVL 16

    Expert Comment

    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

    LVL 2

    Expert Comment

    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.
    LVL 1

    Author Comment

    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 = ',,,,,,,'
    --set @strEmail = ''
    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 '', @CSM, @Subject, @strSQL, ',', ''		
    			--exec sp_send_cdosysmail '', '', @Subject, @strSQL, ''	
    			set @strSQL = ''
    			set @Subject = 'PLEASE IGNORE THIS EMAIL: IT IS A TEST'
    			--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 '', @CSM, @Subject, @strSQL, ',', ''
    	--exec sp_send_cdosysmail '', '', @Subject, @strSQL, ''	
    --select * from #tmpresults
    drop table #tmpResults

    Open in new window

    LVL 16

    Accepted Solution

    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).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now