Select previous record in a Select

ivanblue
ivanblue used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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

Author

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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
let me check this out exactly ...
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
If it's slow add an index on vteid + vtedate (+eventually vteid
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
Sorry the comment above went off before correction  
Index on vtecode + vtedate (+ eventually vteip) should help

Author

Commented:
Thanks Guy Hengel [angelIII / a3], the indexes reduced execution time more than 2x :D

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today