Solved

How to get previous record before the last one?

Posted on 2013-12-26
5
316 Views
Last Modified: 2013-12-27
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
Comment
Question by:dimensionav
  • 3
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39740314
The idMain values are not necessarily sequential, correct?  In other words, can the previous row may be several ids back?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 39740349
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
 

Author Comment

by:dimensionav
ID: 39740486
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39740611
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
 
LVL 33

Assisted Solution

by:snoyes_jw
snoyes_jw earned 200 total points
ID: 39740624
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

17 Experts available now in Live!

Get 1:1 Help Now