I was asked by one of my co-workers for some SQL help. He needed a SQL statement that would suppress repeated column values for the result set. Basically the value would be shown for the first row and blanked for each successful row that had the same value. Typically you would handle this in the application code, we had a case where we had to pass data to another application and we needed to do this within a single SQL select statement.

For example if we have the values:

username             Category
-------------------- ----------
Brian                cs
Tom                  cs
Joe                  cs
Allen                cs
Bill                 ts
Steven               ts
Fred                 ts
Ted                  ts

We would want to return this as the output

username             Category
-------------------- ----------
Brian                cs
Tom                  
Joe                  
Allen                
Bill                 ts
Steven               
Fred                 
Ted                  

Using the following table structure:

create table test(id int, cat varchar(10), username varchar(20))

We can make a query like

select t.username
,case
  when t.id = (select top 1 id 
               from test t3 
               where t3.cat = t.cat 
               order by t3.cat, t3.username) then t.cat
  else ''
end as Category
from test t
order by t.cat, t.username

What the case keyword is doing is a sub-select on the same table and uses top 1 to match on only the first row for each set of categories.  If we match, we use the category value, otherwise we use an empty string value.  This is not very inefficient, you are doing the sub-select for each row of the query.  We needed to do this because the situation only allowed a single SQL statement to be executed.  We were working with a small set of records and this executed without any delay.

If you can call a stored procedure or execute a batch of SQL, you can split this up and gain a performance increase for larger sets of data.  Instead of doing the sub-select on each row, populate a table variable with the first row for each category.  Then do a left join from the main table to the table variable.  The combined SQL would look something like this:

declare @q table(cat varchar(10), username varchar(20))
insert into @q(cat, username)
select t.cat, MIN(t.username)
from test t
group by t.cat

select t.username, COALESCE(q.cat,'') as Category
from test t
left join @q q on t.cat = q.cat and t.username = q.username
order by t.cat, t.username

Another way to get this affect is to use a Common Table Expression (CTE) as part of the query.  This would behave like the table variable, but you would have just a single select statement.  This would be useful for reporting tools where you can only specify a single SQL statement to retrieve the data.

Using the above example data, the new select statement would look like this

with cte as
(
  select cat, min(username) as username
  from test
  group by cat
)
select t.username, COALESCE(c.cat,'') as Category
from test t
left join cte c on t.username = c.username and c.cat = t.cat
order by t.cat, t.username

This query should be perform well (if not better) as the query with the table variable.

I upgraded to Firefox 3.6.3 the other day and at the same time, upgraded one of the add-ons I use (Xmarks).  I love Xmarks, but that’s for another time.  When Firefox upgrades an add-on, it displays the Add-ons list, which is a dialog box that lists all of the add-ons that are registered with Firefox.  You can use that dialog to enable or to disable an add-on or see if a newer version of an add-on is available. 

Firefox has two terms for add-ons.  They refer to them as either an add-on or an extension.  They mean pretty much the same thing with Firefox and I’m going with the term that appears in their own documentation, which is extension.

Something was changed in Firefox with the 3.6 release.  I saw a bunch of extensions that were not there before.  I was seeing multiple versions of something called the “Java Console”.

This add-on is a Java development tool.  You can use it display error messages from Java applets running on a page.  I never use it and I didn’t want every old version in that list. If you search on “firefox java console multiple” with your favorite search site, you’ll get over 100,000 hits.  So I figured that it wasn’t me, it was Java causing the problem.  One the higher ranked hits for the search took me me to an article about Java in the Mozilla (people behind Firefox) knowledge base.

It appears that when the Java installer is upgrading from a previous version of Java, it’s smart enough to remove or overwrite the previous version of the Java runtime, but not smart enough to remove the previous version of the Java Console extension. The KB article about Java has a link for how to manually uninstall a add-on.  You can do it manually or from within Firefox.  I was unable to remove it through Firefox, so I decided to remove it manually with extreme prejudice.

You can register an extension with Firefox in multiple ways.  With Java, the extensions are stored in the following folder:  “%ProgramFiles%\Mozilla Firefox\extensions”.

On my XP machine, a listing of that folder displayed the following:

Volume in drive C is JOHNSONWAX     Serial number is 00a4:443c
 Directory of  C:\Program Files\Mozilla Firefox\extensions\*

 4/23/2010   9:42             .
 4/23/2010   9:42             ..
 4/16/2010  10:57             {972ce4c6-7e08-4474-a285-3208198ce6fd}
11/18/2008  12:37             {CAFEEFAC-0016-0000-0010-ABCDEFFEDCBA}
 2/18/2009  16:52             {CAFEEFAC-0016-0000-0011-ABCDEFFEDCBA}
 5/28/2009  10:31             {CAFEEFAC-0016-0000-0013-ABCDEFFEDCBA}
 8/10/2009  14:35             {CAFEEFAC-0016-0000-0015-ABCDEFFEDCBA}
11/23/2009  10:29             {CAFEEFAC-0016-0000-0017-ABCDEFFEDCBA}
 3/31/2010  13:57             {CAFEEFAC-0016-0000-0019-ABCDEFFEDCBA}
 4/22/2010  11:33             {CAFEEFAC-0016-0000-0020-ABCDEFFEDCBA}
10/09/2009  14:17              49  {E0B8C461-F8FB-49b4-8373-FE32E9252800}
             49 bytes in 1 file and 10 dirs    4,096 bytes allocated
 20,867,198,976 bytes free

From this listing, the multiple Java Console entries jump out as because of the multiple folder names that start with “{CAFEEFAC-0016”.  The folder names are GUIDs, and it looks like Sun has embedded the version number in to the GUID name.

You typically will not see the name of the extension in the folder or in any of the file names in the extension folder.  You need to check the folder and read either the chrome.manifast or the install.rdf, files typically found in an extension folder.  When I opened the install.rdf (this file tells FireFox how to register the extension) in the {CAFEEFAC-0016-0000-0010-ABCDEFFEDCBA} folder, this is what I saw.

http://www.w3.org/1999/02/22-rdf-syntax-ns#"
     xmlns:em="http://www.mozilla.org/2004/em-rdf#">
  
    Java Console
    {CAFEEFAC-0016-0000-0010-ABCDEFFEDCBA} 
    6.0.10
    2 
    true
    
      
        {ec8030f7-c20a-464f-9b0e-13a3a9e97384}
        1.0
        5.0+
      
    
        

The fourth line down lists the extension name as the value of the em:name element and two lines down, you’ll see the version number as the value of the em:version element. Since I could tell that the “{CAFEEFAC-0016” folders belong to the Java Console, all I had to was to delete all of the “{CAFEEFAC-0016” folders, except for the last one.  Once I did that, all I needed to do was to restart Firefox and the extra extensions were gone.

This appears to be a bug with the Java installer when you upgrade over an existing version.  If it’s smart enough to remove the previous runtime, it should be able to remove the previous version of the console.  It would not have been that hard, all they had to to was look at all the extension folders that started with “{CAFEEFAC-0016-0000” and remove the one that was not the current one.

I have a love/hate relationship with Twitter.  Some days, I think it’s greatest time waste since they invented the meeting.  Other days, it’s an endless source of amusement.  And on some days, it’s actually useful.  Yesterday was one of the useful days.

At about 9m last night, my 10 year daughter came running into my home office.  Kathryn was convinced that she had broken the TV.  Since the destruction of electronic devices is her sister’s department, I went in to the bedroom to take a look.  Kathryn was watching a show on TLC HD and it was not coming in well.  The audio was distorted and the image had tiling artifacts.  I assured my daughter that it wasn’t her fault and sent her off to read a book.

I checked a few channels and sure enough, it wasn’t just TLC HD that was having problems.  Most of the channels had the same problem.  And yet, a small number of channels were just fine.  My decidedly non-scientific search had determined that the SDV channels were having the problem and the non-SDV channels were just fine. 

SDV or Switched Digital Video is a technical hack that allows cable companies to provide more channels than they actually have the physical bandwidth to carry them to every home.  What they do is to only provide a channel when a view switches to that channel.  A cable node will supply somewhere between 1000 to 2000 homes in a neighborhood.  If the cable company provides 200 channels, only a fraction of that number is actually being watched at any time. 

The “Switched” part of SDV means that the frequency that the channels goes out on is not fixed, it can changed based on usage.  When you select a SDV channel from your set top box, the channel request goes back to the cable office.  They check to see if any other box on your node is watching that channel.  If it’s in use, the cable office sends back the frequency that the channel is on, and you get to watch “Matlock”. 

It’s a clever hack, but it’s still a hack.  If you get enough people on your node to watch a different channel, it will fall apart like a cheap suit.  While I doubt that would actually happen, it’s technically feasible.  They can spend the money running Fiber optic to each node, and making the last run to house over copper.  Running fiber to each house is very expensive.  Just ask Verizon how much it costs to wire a neighborhood for FiOS.

If no one else is currently watching that channel, the cable company assigns the channel to an open frequency for your node and sends that frequency back down to your set top box.  If you have a TiVo, the process works the same way.  Your cable company provides a device called a Tuning Adapter, that manages the frequency negotiation between the TiVo and the cable office.

I have both a set top box and a TiVo HD with a Tuning Adapter.  The set top box was having the audio distortion and video artifacts.  The TiVo wasn’t even displaying the SDV channels.  Since the non SDV channels were crystal clear, I guessed that it wasn’t a signal problem and the problem was more likely than not an issue at the cable office.  Since I could reproduce the problem with just the cable company’s equipment, I decided to call their tech support line.

My cable provider is Time Warner Cable of Albany, NY.  I went to their web site to look up their support number.  I called it and heard a recorded message explaining that there would be a long wait time for technical support.  TWC Albany is pretty well staffed, that only happens when something bad has happened. 

While I was in the hold queue, I saw an option for chat support on their web site.  I tried that and the best thing that I can say about that experience was that it occupied 45 minutes of my time without any resolution.   The sum of the support rep’s skill set was to have me reboot the set top box and when that did not resolve the issue, he then scheduled a service call for a time slot that was 9 hours in the past.  Since my cable package did not include any time travel options, I wrapped up that chat session and tried calling the Albany support number again.  This was about 10pm.

While I was in a never ending on hold queue, I decided to see who from Time Warner Cable was on Twitter at that hour. As it turns out, the corporate office of Time Warner Cable has a bunch of people on Twitter.  Their main support account is @TWCableHelp and I could see on Tweetdeck that they were active at this late hour.  I tweeted the following message:

Can @TWCableHelp help with local issues? My SDV channels are unwatchable and I made the mistake of trying TWC online chat

The message was a little snarky, but 45 minutes of chatting with a CSR in India will do that to you.  Within two minutes, I got the following response:

@anotherlab Can certainly take a look into your issue. Had eChat already rebooted the box? ^BP

We then traded a few tweets and Bryan (the ^BP in the message) ended up calling me.  He was very professional and had a pretty good sense of humor.  We immediately ruled out time travel as an option and he was able to remotely check the signal strength coming into my set top box.  Everything looked normal.  While we were discussing what the root cause could be, the problem cleared up.  What ever had been going on at the local office, the problem appeared to have been resolved. 

We checked the channels on the set top box and on the TiVo.  From his end, he could see that about 50 channels where being sent over SDV on my node.  So we picked a few channels were not being sent at that moment. Everything was normal. 

We chatted a bit about my TiVo and I mentioned that a few people had been complaining about the latest software update that went out to the DVRs that TWC supplies.  He wanted to know more about those complaints.  I told him that I didn’t know too much about those problems, but I had read a few complaints on some local sites.  I gave him a few links (Kristi Gustafson at the Albany Times Union and Albany HDTV) and he checked them out while we were talking.

Bryan then asked if I had any other questions.  So I asked him about the CCI Flag.  Almost all of the digital channels (except for local stations) carried by Time Warner Albany have the CCI Flag set to 0x02, which is usually referred to as “Copy Once”.  “Copy Once” means that the show can be recorded to a DVR, but it can not be copied from the DVR.  The DVR must respect the CCI Flag, otherwise it would not be allowed to use a CableCARD, which is needed to access the digital content.

This is not an issue with the DVR that the cable company supplies, but TiVo can do quite a bit more than a cable company DVR.  If you have multiple TiVo DVRs in your house, you can copy shows from one to another.  If you have young children like I do, that is a very handy feature.  We can also copy the shows to a PC for storage and transfer them back at a later date.  The video files are copy protected, it’s not like you can just upload them to the great unwashed masses on the Internet.

I have a couple of TiVo boxes and it’s been handy to watch a show recorded on one TiVo from another.  When the CCI flag is set to “Copy Once”, the only copy allowed is the copy on the first TiVo.  It just wont copy it to another TiVo in the house.  Many cable providers set most of the channels (excluding Pay Per View and premium channels like HBO) to 0x00, which is usually called “Copy Freely”.

Bryan told me that it was a local cable office decision. I told him that the local cable office had made it clear to me that the decision was mandated by Time Warner Corporate.  He said that he would look into it and then would get back to me on that.  i think that he will find that it’s a dead end, but it was worth a shot.

All and all, I was very satisfied with the support provided by Time Warner Cable through their Twitter account.  But please change the CCI flag.