Solved

Select previous record in a Select

Posted on 2014-02-25
11
300 Views
Last Modified: 2014-03-01
Hello, I need to show a value from the previous record to every record where the field VteIP is the same in both in MySQL. This is my structure and data:

VteCod	VteIP			VteDate
4063	181.124.175.109		17/02/2014 10:44
3887	181.125.217.67		17/02/2014 07:27
3827	181.125.220.6		17/02/2014 05:36
5916	181.125.6.149		19/02/2014 13:45
4157	181.40.102.156		17/02/2014 11:16
4171	181.40.102.156		17/02/2014 11:18
4175	181.40.102.156		17/02/2014 11:19
4245	181.40.102.156		17/02/2014 11:31
4248	181.40.102.156		17/02/2014 11:31

Open in new window


And the result fields should be:

VteCodPrevious, VteIPPrevious, VteDatePrevious, VteCod, VteIP, VteDate

Thanks!
0
Comment
Question by:ivanblue
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39888098
Can you give an example using your data above of how you want the output to look.

Given that dates are different for same IP and you only want one date in the result row - which one do you want to select.

Based on your description and test data this is what I envisage
4063                     181.124.175.109  17/02/2014 10:44
3887                     181.125.217.67   17/02/2014 07:27
3827                     181.125.220.6    17/02/2014 05:36
5916                     181.125.6.149    19/02/2014 13:45
4171 4157 4175 4245 4248 181.40.102.156   ?????

Open in new window

I don't think you will be able to achieve this with a query. You will need to do it with code.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39888161
you can do with SQL, but it's heavy, as MySQL doesn't (yet) have the LEAD() or LAG() functions Oracle has.

but there are solutions, see for example this one doing plenty of explanations:
http://explainextended.com/2009/03/12/analytic-functions-optimizing-lag-lead-first_value-last_value/

in short, the main question is to know what exactly you want from the "previous" record?
1 column, or several columns.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39888171
I think he answered that already

And the result fields should be:

VteCodPrevious, VteIPPrevious, VteDatePrevious, VteCod, VteIP, VteDate

The reptition of VteCodPrevious would seem to indicate as many columns as there are rows with teh same IP. So your result set is giong to have each row having a variable and unknown number of columns.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39888586
You could first create a view of the ranked records, then self join to row with previous rank.
http://www.experts-exchange.com/Database/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
0
 
LVL 1

Author Comment

by:ivanblue
ID: 39888607
Guy Hengel [angelIII / a3] I want VteCod, VteIP, VteDate from the previous record.

julianH I just need the inmediate previous to every record if they have the same VteIP, so there won't be more than six columns.

Kevin Cross, I think your strategy can work. I'll try it out.

Thanks.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39894186
let me check this out exactly ...
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39894203
this should be working ...
 select p.VteCod	VteCodPrevious
 , p.VteIP VteIPPrevious
 , p.VteDate  VteDatePrevious
 , t.VteCod	
 , t.VteIP
 , t.VteDate
from your_table t
left join your_table  p
   on p.VteCod	 = t.VteCod	
	and p.VteDate = ( select max(x.VteDate)
	   from your_table  x
		 where x.VteCod	 = t.VteCod	
		  and x.VteDate < t.VteDate
	) 

Open in new window

0
 
LVL 1

Author Comment

by:ivanblue
ID: 39896468
Hello Guy Hengel [angelIII / a3], your query gave me NULL in every previous field, so it didn't work. But the approach was the correct one, so the points are yours. I use your query to create the one that works, it takes a lot of time but it works.

Here's the resulting query:

 SELECT
	p.VteCod VteCodPrevious,
    p.VteIP VteIPPrevious,
    p.VteDate  VteDatePrevious,
    t.VteCod,
    t.VteIP,
    t.VteDate
FROM 
	vote t
LEFT JOIN vote p ON p.VteIP = t.VteIP AND 
	p.VteCod = (
    	SELECT 
        	MAX(x.VteCod)
        FROM
        	vote x
        WHERE 
        	x.VteIP = t.VteIP AND
            x.VteCod < t.VteCod
    )
ORDER BY
	t.VteIP ASC,
    t.VteDate ASC

Open in new window


I wouldn't make it without you. Thanks! :D
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39896759
If it's slow add an index on vteid + vtedate (+eventually vteid
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39896762
Sorry the comment above went off before correction  
Index on vtecode + vtedate (+ eventually vteip) should help
0
 
LVL 1

Author Comment

by:ivanblue
ID: 39897110
Thanks Guy Hengel [angelIII / a3], the indexes reduced execution time more than 2x :D
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

13 Experts available now in Live!

Get 1:1 Help Now