Subscribe Now: Feed Icon

Sunday, March 4, 2012

Selecting Distinct Rows

Or how the definition of the request can get us confused?

One of my team members asked me today to help her in a SQL query and although I hate SQL I tried to help her. She wanted to select a row by a distinct field – ColumnA and to view other columns in that row, so I started the query with the distinct keyword:

  1. SELECT DISTINCT ColumnA
  2. FROM SomeTable

But run into the problem that she wanted those other columns of the row which weren’t distinct.

So I Googled Distinct and got lost. After taking a coffee break I changed my search to “sql select id of first distinct” the first link was of StackOverFlow: SQL SELECT DISTINCT ID from copies ORDER BY CID

Which told me of course that I was using the wrong keyword and should have used the Group by keyword.

The end result query:

  1. SELECT *
  2. FROM SomeTable
  3. WHERE OBJECTID IN ( SELECT MIN(OBJECTID)
  4.                     FROM SomeTable
  5.                     GROUP BY ColumnA)

Was what she wanted.

God bless coffee (and StackOverFlow)…

Resources:

StackOverFlow - SQL SELECT DISTINCT ID from copies ORDER BY CID

Saturday, March 3, 2012

SQL Server 2008: Forget a user/password

For a long time now I am stuck with a saved user in SQL Server 2008 that I just didn’t want to use:

image

Once upon a time I used that user (that has almost no permissions) for just one action – unfortunately I checked the “Remember password” and whenever I choose that server in the servers combo box I got that damn user. Well I finally had enough!

So I Googled “remove sql server 2008 user password Remember password” and found this StackOverFlow post.

The solution is fairly easy, just rename: C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin to SqlStudio.bin0 (as a rule I never delete a file I don’t have a backup to). And now SQL Server doesn’t remember anything (not even the servers I was on):

image

But that is just a hundred times better than the previous headache…

 

Resources:

StackOverFlow: Removing the remembered login and password list in SQL Management Studio