Solved

Select previous record in a Select

Posted on 2014-02-25
11
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 57

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 143

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 57

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 60

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
 
LVL 143

Expert Comment

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

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 143

Expert Comment

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

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

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 54
MS Access - need to reduce row size 25 74
SQL querys that gives me from one table into another. 2 49
Concat multiple records into one line 3 66
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

731 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