Home » SQL & PL/SQL » SQL & PL/SQL » Select Previous Record!?
Select Previous Record!? [message #312399] Tue, 08 April 2008 15:20 Go to next message
sporkit
Messages: 4
Registered: April 2008
Junior Member
    SELECT DISTINCT ISSUE_VOLUME, ISSUE_NUMBER
    FROM ABSTRACT
    ORDER BY ISSUE_VOLUME ASC, ISSUE_NUMBER ASC


issue_volume	issue_number
------------	-----------
39		1
39		2
40		1
40		2
40		3
41		1
41		2
42		1
43		1


Lets say the only data is have is the fact that I'm on vol 42, iss 1. The previous issue is vol 41, iss 2. How on earth do I write a sql statement to select vol 41, iss 2: Given I only know I'm on vol 42, iss 1.

I've been banging my head against the desk for the past hour! Any help appreciated! I'd rather not do this in my scripts.

Thanks


Lets say in a jsp script I know im on abstract volume 42 issue 1. The
Re: Select Previous Record!? [message #312401 is a reply to message #312399] Tue, 08 April 2008 15:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'd want to use the LAG function.
Re: Select Previous Record!? [message #312410 is a reply to message #312399] Tue, 08 April 2008 16:01 Go to previous messageGo to next message
sporkit
Messages: 4
Registered: April 2008
Junior Member
Thanks for the suggestion!

SELECT PREV_VOL, PREV_ISS
FROM
(
  SELECT V,I, PREV_VOL, LAG(I,1) OVER (ORDER BY V ASC, I ASC) "PREV_ISS" FROM
  (
    SELECT V, I, LAG(V,1) OVER (ORDER BY V ASC, I ASC) "PREV_VOL"
    FROM
    (
        SELECT DISTINCT ISSUE_VOLUME AS V, ISSUE_NUMBER AS I
        FROM HSR_ABSTRACT
        ORDER BY ISSUE_VOLUME ASC, ISSUE_NUMBER ASC
    )
  )
)
WHERE V = 42
AND   I = 1


Still though. There just HAS to be a prettier way of doing this...
Re: Select Previous Record!? [message #312416 is a reply to message #312410] Tue, 08 April 2008 16:58 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
The need for a preliminary DISTINCT complicates things - is that necessary?

The ORDER BY in the innermost inline view isn't doing anything, and I'm puzzled by the two levels of nesting. Couldn't it just be

SELECT prev_issue_vol, prev_issue_num
FROM   ( SELECT issue_volume, issue_number
              , LAG(issue_volume) OVER(ORDER BY issue_volume,issue_number) prev_issue_vol
              , LAG(issue_number) OVER(ORDER BY issue_volume,issue_number) prev_issue_num
         FROM   hsr_abstract )
WHERE  issue_volume = 42
AND    issue_number =  1

Re: Select Previous Record!? [message #314117 is a reply to message #312399] Tue, 15 April 2008 16:08 Go to previous messageGo to next message
sporkit
Messages: 4
Registered: April 2008
Junior Member
Hi guys,

sorry for the late response. essentially yes i had to sort on both the columns. a publication i help administer is published by volume and issue which together creates a kind of primary key.

but this proved to be a semi elegant solution and kept a lot of ugly code out of a script of mine.
Re: Select Previous Record!? [message #314121 is a reply to message #314117] Tue, 15 April 2008 16:13 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I meant the ORDER BY clause in the innermost query, which does not do anything. You'll notice it is not present in my version. Does that still work?

[Updated on: Tue, 15 April 2008 16:14]

Report message to a moderator

Re: Select Previous Record!? [message #314126 is a reply to message #312399] Tue, 15 April 2008 16:57 Go to previous message
sporkit
Messages: 4
Registered: April 2008
Junior Member
Ahh yes.

As far as I could tell it seemed to be a required field in each sort. And if I remember correctly I did have to keep my results ordered properly to make the selection work.

http://www.adp-gmbh.ch/ora/sql/analytical/lag.html

The whole idea is to select the Issue and Volume number of the previous issue. I sorted out all the duplicates and could pick my current record. The previous issue was just one row less than what the biggest record was, yet I couldn't figure out how to grab that information.
Previous Topic: Trying to sort within a multiple UNION sql
Next Topic: bulk collect problem
Goto Forum:
  


Current Time: Thu Dec 08 21:53:51 CST 2016

Total time taken to generate the page: 0.09614 seconds