Monthly Archives: November 2011

SQL Server / Oracle: DISTINCT or GROUP BY

Firstly, sometimes sticking in a DISTINCT is quick and easy, but so easily missed when reading the SQL and also conversely the group by can be pretty long if there are a lot of columns selected.

Firstly a basic example:
SQL Server

SELECT
a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b
GROUP BY a

SELECT
DISTINCT a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b

Oracle

SELECT
a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b
GROUP BY a;

SELECT
DISTINCT a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b;

They both return the same result, simple, now imagine a lot more columns in the select and make your choice of DISTINCT or GROUP BY carefully.

Is COUNT(DISTINCT a) the same as DISTINCT COUNT(a)?
No, the former you select distinct then count them, the later you count then select the distinct counts, so results are 2 and 3.
Also you can’t swap DISTINCT for a GROUP BY in this case, because the GROUP BY would return you two counts, one for each grouped value, however using a nested query you could select the count from the grouped values, like so:

SQL Server

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a
	UNION ALL SELECT 1 AS a
	UNION ALL SELECT 2 AS a) b
	GROUP BY a
) c

Oracle

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 2 AS a FROM DUAL) b
	GROUP BY a
) c;

I always try to remind people that DISTINCT works on what is selected, don’t just go sticking it wherever you feel like as I won’t see it, put it right after the SELECT as a means of helping me see it.

There is also the group by pitfall of grouping on something that you aren’t selecting. Let’s say you originally select two columns grouping on both so you get distinct values, great, then you decide, actually I am only interested in the first column so the second is removed from the select, unless it is also removed from the group by the results will not be the same as distinct! This sort of thing normally leads to the group by being dropped and replaced by distinct.

I hope this helps.

Advertisements

What day is it too foggy to overtake a cyclist on a blind corner?

There is nothing new in impatient drivers overtaking when they really shouldn’t, generally resulting in the oncoming traffic honking their horn in anger (or worse).
Drivers should take more care when overtaking on a foggy day, but a driver this morning was really taking a big risk. It was a really foggy start to the day and the road surface was damp and slippy in places. The road was an S bend and blind from where he/she began overtaking, I was in the middle of the road (my side if that makes sense) as I was shortly going to be taking a right hand turn (in the middle of the S bend). My road position should have been enough to indicate that overtaking wasn’t a good idea, but I think the oncoming car gave him/her realisation of this, luckily there was no collision and I was safe (I think).

I’m looking forward to computer controlled cars made out of polystyrene driving at 20 MPH since people are incapable of driving cars safely.

Another pair of headphones broken

Last night when I got home I took off my headphones and placed them on top of my bar bag, I normally place them inside, but last night I was probably not paying a lot of attention. By the time I had walked the bike from the gate to the shed the headphone cable had tangled in the front wheel and snapped. Gutted.
They were good headphones (compared with the rubbish I normally buy) and I now have to go back to using some really rubbish ones, though it won’t be long before I’ve broken those too given my past history. Christmas is coming, but I won’t be getting any headphones 😦

The mp3 player is still going, so keeping it dry inside the bar bag is working a treat.

There is no chance of the headphones getting caught in the wheel whilst cycling because they are in my ears, so no danger to me from using them.

Windows 8 Developer Preview – More on keyboard shortcuts

In my Windows 8 Developer Preview Walkthrough post I mentioned a few shortcuts, there are lots of shortcuts, but I thought I’d summarise some useful ones here:

To search for an application: WinKey then just start typing
Press WinKey then release then type COM (in upper or lower case) to get a list of apps containing words beginning with COM, first in my list is Command Prompt. I could also type PROM, but notice that ROM doesn’t show anything. Which might be annoying if the app is about cycling but called bicycling then you wouldn’t find it.

To search for settings: WinKey + W
Press and hold WinKey then press W once and release the WinKey, then just start typing, so type POW and you will see Power Options in the list. I fail to remember (every time) that Control Panel is an app, so when searching on settings I don’t find it, but thankfully the search is clever enough to show there is an item in Apps by marking that icon with the matching number too, so I see, hmm, Settings 0, Apps 1, let me click Apps and voila, there it is.

To search files: WinKey + F
Press and hold WinKey then press F once and release the WinKey, then just start typing. This one is nothing new, but if you can’t remember the other two shortcuts, then use this old faithful and click on the Apps/Settings or whatever.

To show the desktop: WinKey + D
Again, nothing new and indeed a shortcut I rarely used, until now. This one really comes into it’s own, can’t find the Desktop tile, can’t be bothered to reach for the mouse, then this one is your new friend.

To bring in the settings bar: WinKey + I
Oh look, the power button!

And my classic favourites…

Active application switch: ALT + TAB

Task Manager: CTRL + SHIFT + ESC

Lest not forget ALT + F4 to close the current window.
Tip: Getting out of Windows 8 (a.k.a. shutting down) is a bit of a pain to get (via the settings screen), but this shortcut gets you there the old way. So WinKey + D to show desktop (if required), then ALT + F4 and there is the old style Log Off / Shutdown / Restart box.

To lock the screen: WinKey + L
Tip: Rather than sliding the lock screen upwards to reveal the logon prompt, just hit Enter.

To open the run dialog: WinKey + R
Also remembering useful apps like mstsc etc

To open Windows Explorer: WinKey + E

Lesser known (more recent) is the numbers…

To open a pinned application: WinKey + 1/2/3/…
Press and hold WinKey then press 1 once and release the WinKey.
I have pinned Google Chrome pinned to my taskbar in the third place, so WinKey + 3 gets me there, even from the start screen!

I will try and add more when I remember, but also if you know of any please comment and I will add them.

Export SQL Server data and import into Oracle

It’s worth making a note of characterset issues as we come across them.

Let’s say I have some data in SQL Server:

SELECT 'Jörg' AS Name
UNION SELECT 'Jones' AS Name

And I export it using the “Save Results As…” option and save as test.csv.

Great, now we have this unicode file and we need to import it, for the unicode character to be supported by Oracle SQL Developer there is a setting you may need to change.
With the default setting of Cp1252:

when I open the test.csv I see:

Those with a keen eye will spot the Byte Order Mark (BOM), but ouch, look at what has happened to Jörg.

So let’s change the Encoding setting to UTF-8:

Then reopen and voila:

The Oracle SQL Developer Data Import Wizard can trick you too, if Encoding setting is set to default then it won’t be the UTF-8 we’ve set, you will need to manually change this to UTF-8 each time you do an import:

Brake pad/block performance

Cheap brake blocks are rubbish, they either seem to be made of solid plastic and don’t grip the rim that well or are made of a soft rubber and wear away quickly.
The pads that my previous bike came with (from new) only lasted 2,000 miles and this more or less fit inline with previous cheap pads I’d had experience of. I was wondering if I needed to spend a bit more and get superiour braking performance and longer life. I splashed out on some multi compound pads and at just under £20 x 2 they weren’t cheap:

Now they have worn out I can give my true verdict, and that is 10,000 miles! They were adequately looked after and swapped from front to back and back again a couple of times so they wore evenly. I have now bought replacement pads (which are only slightly cheaper than the pads and blocks together) and will continue to do so as they are far better than the cheap ones.

To increase life there are two main things I try and do as often as I can be bothered:
– Remove aluminium shavings that get lodged in the pad surface, these are caused firstly by grit then by the shaving scraping more braking surface away. Use a pin or knife point (etc) to carefully “dig” out the shavings. Some people file the surface down, but that removes too much of it. Doing this will increase both rim and pad life.
– Clean those rims regularly! In winter and dirty weather all that dirt grinds away your pads and rims.

Lastly, if your brakes are adjusted and maintained suitably you will be safer as your stopping distance will be reduced. I’m not going to go as far as saying the pads I have stop better than any other pad, but my like for like comparison when I got them was that they really did reduce my stopping distance compared with cheap pads.

For ease of adjustment I would recommend parallel push v-brakes, they require less tweaking as the pad wears. Standard v-brakes and cantilever brakes swing at the rim in an ark, so as the pad wears they hit the rim lower and lower each time so they require adjustment to ensure they hit the middle of the rim. Whereas parallel push brakes hit the rim horizontally each time so only a barrel adjustment is required. At the moment I have a parallel push v-brake on the front and a standard v-brake on the back, I wish I could get the Shimano XTR v-brakes like a friend of mine, but I’m also saving up for other components and they are low on the priority list (for now).

Unit Test Projects and NUnit compatibility

I often need to write Unit Tests and have them accessible from NUnit as well as the Visual Studio Test View (etc), so I thought I’d write myself a walkthrough of the common things I have to do.

Firstly, whichever language I’m using, Visual Studio is normally configured to create the wrong one, so let’s say I’ve got my C# hat on and I create a test project:

Darn it, it’s created a VB.NET one, so I delete it and search for the setting or way I create a C# one. This can be located under Tools -> Options -> Test Tools -> Test Project, then choose your preference from the “Default test project language” dropdown, like so:

You will also notice you can choose what (if any) template test classes are created for you. 99% of the time I use Unit Test and that is it.
So you go back in and create the project again and it’s created a C# one, it automatically adds a reference to the Visual Studio Test classes, great:

Add a reference to NUnit, so you have:

Now let’s look at the Unit Test class, I’ve stripped it down to the bare bones:

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace TestProject1
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
        }
    }
}

Since we don’t want the Visual Studio and the NUnit classes to conflict my approach is to take out the Visual Studio using statement like so:

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;

namespace TestProject1
{
    [Microsoft.VisualStudio.TestTools.UnitTesting.TestClass]
    public class UnitTest1
    {
        [Microsoft.VisualStudio.TestTools.UnitTesting.TestMethod]
        public void TestMethod1()
        {
        }
    }
}

Now this will still work as before, but we can add NUnit in now too, like so:

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using NUnit.Framework;

namespace TestProject1
{
    [Microsoft.VisualStudio.TestTools.UnitTesting.TestClass]
    [TestFixture]
    public class UnitTest1
    {
        [Microsoft.VisualStudio.TestTools.UnitTesting.TestMethod]
        [Test]
        public void TestMethod1()
        {
        }
    }
}

And proof of the pudding, Visual Studio:

NUnit:

This also reminds me, while I’m on the subject, to be able to open a Visual Studio project from within NUnit there is a setting you need to change, go to Tools -> Settings -> IDE Support -> Visual Studio and then tick the “Enable Visual Studio Support” checkbox, like so: