They say you learn something new every day.

Posts tagged ‘sql’

Second Highest Value (02/04/2012)

I had to get the second highest value from a database today. Something that I thought was going to be really difficult. But actually, it wazs fine:

SELECT MAX( col ) FROM table WHERE col < ( SELECT MAX( col ) FROM table )

Table Details (07/03/2012)

I spent a silly amount of time yesterday troubleshooting an access error. It kept saying I had an error in my SQL, yet I knew it was correct.

In the end, it turned out one of the columns had a reserved name.

The moral of the story is, always enclose your column names with square brackets:

SELECT [Column] FROM [Table] 

It’s easy to get lazy. Just don’t. Always put square brackets round, and you won’t have this problem.

Listing Tables (15/01/2012)

Occasionally, I’ve thought it would be useful to list all the tables in a database, but I’ve never actually tried to do it. It turns out there are different ways for different database engines.

Yesterday I was trying to list all the tables in access and I came across this code:

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
  “Provider = Microsoft.Jet.OLEDB.4.0;Data Source = ‘C:\Scripts\Test.mdb’” 

Set objRecordSet = objConnection.OpenSchema(20)

Do Until objRecordset.EOF
   Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)

I’ve cleaned it up a bit, but it’s amazing – you just have to enter “20” to get a list of tables.

You do get system tables as well, so you need to filter them out, but this is great (at least for Access).

Tag Cloud