random subsets of data ii

In doing more work to get random subsets, the previous solution fails miserably if you are going to be using a view. You can run the EXEC statement in a stored procedure but cannot use it in a view. And furthermore, calling a stored procedure from a view is somewhere between extremely problematic and impossible, depending on who you talk to. Using the RANK function and partitioning the data you can get a similar result.

Let us use the same idea as the previous example and assume we want at least one employee from every department. The only requirement for the following query is that you are asking for more employees than you have departments.

select top 100 T1.* from

(select 

RANK() over 
(PARTITION by Department order by newid()) as r,  

* 
from Employees
where Active = 1

) as T1
order by t1.r, t1.Department

Note, as before, that this is a sql server query. Modifications may need to be made for different flavors of SQL. What this is doing is for every department the employees are getting randomly ranked thanks to order by NEWID(). By selecting the top 100 and ordering by rank and then department, you'll get all of the 1's from every department, then all of the 2's and so on until you get 100.

The order by department is strictly unnecessary. I did it to make viewing and verifying the results easier.

random subsets of data

I recently had a request to return a random subset of contacts from a database, but to be sure that there were some contacts from each group represented. Let us say that you have a database with employees and you want to get a list of some random people from each department. Here is how I went about doing it:

declare @s1 varchar(max)

select @s1 = coalesce(@s1 + ' union all ', '') +
e from (

select distinct
'select * from (select top 11 percent * from 
Employees where Department = '''
+ Department + ''' order by newid()) 
as [t' + Department + ']'  as e
from Employees) T1
exec (@s1)

What we are doing here is generating the text for a query that will select the top N contacts from the Employee list for each department. The coalesce function will put all of these queries together into one query that will union the results into a single table. The exec function will execute the query.

Note that this is a TSQL example to run on SQL Server. You may have to translate this, depending on your database system.

If you want a fixed number then what I would do is tweak the percent to get a number than is slightly more than the number desired then just select the top N from that. This runs the risk of not getting somebody from every department. Another method would be to select a number slightly less than your target, then select some random number of records to add to the result. This is more complex, but may be what you need.

Returning data tables from Web Services in .NET

This is just a small reminder, and I don't know why it gets me every time, but it does. I guess I return data tables from web services infrequently enough where I don't think about it. Maybe writing it down somewhere will help me remember. When creating a data table to return from a web service, give the table a name.


DataTable dt = new DataTable("nameMyTable");


If you don't, the first time you hit the service when testing you will get an exception about the table not having a name. And then you will curse to yourself and then give it a name and move on with your life.

scripts for processing stuff

Often I need to write something up quick to process a file, or process files in a directory and I'll want a GUI for these things so I can pass them along to other people to use. Here are two python scripts that I use as templates for picking either a file or a directory and then doing something with it. The object here isn't to have everything "correct" according to the latest design fashion or methodology. With this script I can import a python module that takes a file or directory name and does the processing I want, plug in the function name, pass the parameter and then move on with my life.

Pick a file and do something with it:

from tkinter import *
from tkinter.messagebox import *
from tkinter.filedialog import *

class FileProcessor(Frame):

# Object constructor
    def __init__(self, parent=None):
        Frame.__init__(self, parent)

        self.txtFile = Entry(parent)
        self.txtFile.place(x=6,y=64,width=375,height=24)
        self.txtFile.insert(0,'')

        self.btnPickFile = Button(parent,text='Pick File', command=self.btnPickFileClick)
        self.btnPickFile.place(x=6,y=20,width=96,height=24)

        self.btnRun = Button(parent,text='RUN', command=self.btnRunClick)
        self.btnRun.place(x=6,y=114,width=96,height=30)


# Methods (event handlers) of object
    def btnPickFileClick(self):
        print (self.txtFile.get())
        self.txtFile.delete(0,END)
        self.txtFile.insert(0,askopenfilename())

    def btnRunClick(self):
        fileName = self.txtFile.get()
        self.runFile(fileName)

    def runFile(self, fileName):
        print('code goes here to run when click: ' + fileName)
        showinfo('The file is',fileName)

# Method called if script is run directly
# instead of imported or used as a class
if __name__ == '__main__':
    root = Tk()
    root.title('Process File')
    myForm = FileProcessor(root)
    myForm.pack()
    root.geometry("423x156")
    root.minsize(423,156)
    root.maxsize(423,156)
    root.mainloop()

Pick a directory and do something wit it:

from tkinter import *
from tkinter.messagebox import *
from tkinter.filedialog import *

class DirectoryProcessor(Frame):

# Object constructor
    def __init__(self, parent=None):
        Frame.__init__(self, parent)

        self.txtDir = Entry(parent)
        self.txtDir.place(x=6,y=64,width=375,height=24)
        self.txtDir.insert(0,'')

        self.btnPickFile = Button(parent,text='Pick Directory', command=self.btnPickFileClick)
        self.btnPickFile.place(x=6,y=20,width=96,height=24)

        self.btnRun = Button(parent,text='RUN', command=self.btnRunClick)
        self.btnRun.place(x=6,y=114,width=96,height=30)


# Methods (event handlers) of object
    def btnPickFileClick(self):
        print (self.txtDir.get())
        self.txtDir.delete(0,END)
        self.txtDir.insert(0,askdirectory())

    def btnRunClick(self):
        dirName = self.txtDir.get()
        self.runDir(dirName)

    def runDir(self, directoryName):
        print('code goes here to run when click: ' + directoryName)
        showinfo('The directory is',directoryName)

# Method called if script is run directly
# instead of imported or used as a class
if __name__ == '__main__':
    root = Tk()
    root.title('Process Directory')
    myForm = DirectoryProcessor(root)
    myForm.pack()
    root.geometry("423x156")
    root.minsize(423,156)
    root.maxsize(423,156)
    root.mainloop()

Putting a string on a single line

Often times text is written on multiple lines for the sake of clarity. Sometimes these lines need to be consolidated to a single line. This happens to me a lot with SQL queries that end up needing to be in a config file somewhere. I'll write them out all pretty so they make sense (as much sense as some SQL can make...) and then I'll need to consolidate that down to one line. Here is a python one liner that does just that, given a string named mystr

" ".join([x.strip() for x in mystr.split("\n")])

Don't make me slap you, Beavis

Every single time somebody asks "how hard can it be?" or "why is the estimate so big?", I want to smack them. Hard. Some folks at 37signals were kind enough to offer an example that answers these sorts of questions when asked about a seemingly simple feature.

I applaud the fine folks at 37signals for publishing things like this.

splitting csv files

I'm sure I've ranted about this before. I'll rant about this again. I just hate seeing examples from people in C# where they say that you just split a CSV file by using mystring.Split(',') and you will get an array where each item is the value for each field. One would assume, since they mention C#, that at some point in their lives they have worked with excel style CSV files that have data like

"Bob","Hello, Dear","""Dude, where's your car"""

The string split method obviously will not correctly handle this case at all. Here is a page I found with a nice little function that works for me in most cases.

http://www.tedspence.com/index.php?entry=entry070604-124237

Hopefully this will work for you, too.