Solved

How to get previous record before the last one?

Posted on 2013-12-26
5
319 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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