• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

How to get previous record before the last one?

HI

I have a table like this named Profiles:

!dMain, IdProfile, Qty
1                17        20
2                17        33
3                18        25
4                18        30
5                17        10
6                17         5

I want to get the previuous record regarding the last added one per each IdProfile using SQL, something like this:

!dMain, IdProfile, Qty
3                18        25
5                17        10

Thanks in advance.
0
dimensionav
Asked:
dimensionav
  • 3
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
The idMain values are not necessarily sequential, correct?  In other words, can the previous row may be several ids back?
0
 
Kevin CrossChief Technology OfficerCommented:
Here is one way to accomplish this.  It works whether or not the previous idMain is close or not.

SELECT idMain, idProfile, Qty
FROM (

SELECT idMain, Qty
     , /* if profile is same as row above,
          increment row number; else, start at 1. */
       if(idProfile = @lastprofile,
          @rownum:=@rownum+1,
          @rownum:=1) as ranking
     , /* since we are using @lastprofile in @rownum,
          we have to set @lastprofile 2nd;
          otherwise, the current and last always would equal.
        */
       @lastprofile:=idProfile as idProfile
FROM eePrvTest, 
(SELECT @rownum:=0, @lastprofile:=null) r
ORDER BY idProfile, idMain DESC

) derived
WHERE ranking = 2
;

Open in new window


The ranking approach is from an article I wrote, so you can read for further explanation:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html

The key is the solution ranks the records in reverse idMain order; therefore, the outer SELECT gets the second ranked row with the first being the last entry.

I hope that makes sense.

Best regards and happy coding,

Kevin
0
 
dimensionavAuthor Commented:
The idMain values are not necessarily sequential, correct?  In other words, can the previous row may be several ids back?
 YES, can be several ids back.

Kevin, just a question, does your solution works for MySQL?
Thanks for everything.
0
 
Kevin CrossChief Technology OfficerCommented:
The solution I gave is specific to MySQL.  It would be different syntax for other languages.  The article I wrote explains the different implementations across different SQL platforms in case you need to create something similar in other systems.
0
 
snoyes_jwCommented:
Here's another way, that does not require variables or subqueries.
SELECT p1.* 
FROM profiles p1 
JOIN profiles p2 ON p1.idMain < p2.idMain AND p1.idProfile = p2.idProfile 
GROUP BY p1.idMain 
HAVING COUNT(*) = 1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now