Skip to main content

delete all the values with in a group keeping only the topmost entry in each group in mysql

sometimes you're caught up in a situation where you have multiple entries against an id and you want to keep only single entry against each.

Table structure would be something like this:

Table `temp`

user_id tstamp source
100 4/26/2015 app
100 4/26/2014 web
100 4/25/2015 app
101 4/26/2015 app
100 4/23/2015 app
101 4/20/2015 web
100 4/1/2015 app
200 4/26/2015 app
200 4/2/2015 app

Desired Output:
user_id tstamp source
100 4/26/2015 app
100 4/26/2014 web
101 4/26/2015 app
200 4/26/2015 app

Now, I want to delete the rows where source is `app` and keep the entry with max tstamp only with in a group.
Here's the sample query to achieve the same:

DELETE tmp FROM `temp` tmp join (select user_id, max(tstamp) as ts from `temp` where user_id in (select user_id from `temp` where source = 'app' group by user_id having count(user_id)>2) and source = 'app' group by user_id )t on tmp.user_id = t.user_id and tmp.tstamp != t.ts and tmp.source = 'app'

Exercise for you: Replace that inner query with join and check the performance benefits ;)


Popular posts from this blog

VBA MAcro to generate "table of contents with hyperlinks" automatically in a ppt

VBA MAcro to generate "table of contents with hyperlinks" automatically in a ppt:

Function TableOfContent(count As Integer)
'count is the no. of slides in ppt
Dim var As Integer
Dim i As Integer, scount As Integer
Dim strSel As String, strTitle As String, strb As String, strtemp As String, str As String
Dim arr() As String
Dim index As Integer, indexcount As Integer, slidecount As Integer
Dim summary As Slide
Dim para As Integer
Dim slideOrder() As Integer

'To generate the Table of contents slide
ReDim slideOrder(count - 2)

'Collect all the IDs of the selected slides
For i = 1 To count - 2
slideOrder(i) = i + 2

'Iterate over the slides in Index order
slidecount = UBound(slideOrder)
For scount = 1 To slidecount
If ActivePresentation.Slides(slideOrder(scount)).Shapes.HasTitle Then
'Build up the ToC Text
strTitle = ActivePresentation.Slides(slideOrder(scount)).Shapes("UseCase").TextFrame.TextRange.Text + ": "
str = ActivePresentation…

error in eclipse on import org.eclipse.jetty.server.Server;

When you find error in line import org.eclipse.jetty.server.Server; it means eclipse doesn't contain jetty.jar. Right click on project -> properties on left pane -> java build path right pane -> add external jar under libraries tab link it to all the jar files of jetty folder. can download jetty from : now rebuild the project.

How to do mass insert in redis

A basic and fairly easy way to do mass insert in redis:

Command to use: echo -e "$(cat redis_mass_insert.txt)" | redis-cli --pipe
Content of file "redis_mass_insert.txt": *3\r\n$5\r\nlpush\r\n$5\r\nu:m:1\r\n$5\r\nvalu1\r\n*3\r\n$5\r\nlpush\r\n$5\r\nu:m:1\r\n$7\r\nmyvalue\r\n

Result: All data transferred. Waiting for the last reply...
ERR unknown command ' *2'
ERR unknown command '$4'
ERR wrong number of arguments for 'echo' command
ERR unknown command '$20'
ERR unknown command ' A�j d�Q;yT��าก �h>'

NOTE: command line will show error but in actual the data is transferred and you can check it by entering in redis.

Here's the snapshot of the whole procedure:

To check how the command is responding: use hexdump -C
Command: echo -n $'$3\r\nset\r\n$3\r\nkey\r\n$5\r\nvalue\r\n ' | hexdump -C
whose output will be something like
00000000 24 33 0d 0a 73 65 74 0d 0a 24 33 0d 0a 6b 65 79 |$3..set..$3..key|
00000010 0d 0a…