Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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!
0
ivanblue
Asked:
ivanblue
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now