Select previous record in a Select

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!
LVL 1
ivanblueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Julian HansenCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Kevin CrossChief Technology OfficerCommented:
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
ivanblueAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me check this out exactly ...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ivanblueAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If it's slow add an index on vteid + vtedate (+eventually vteid
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry the comment above went off before correction  
Index on vtecode + vtedate (+ eventually vteip) should help
0
ivanblueAuthor Commented:
Thanks Guy Hengel [angelIII / a3], the indexes reduced execution time more than 2x :D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.