SQL–how to find nth row / nth order element

Example below is self-explanatory – you should assign rank by desirable criteria and then just group by your categories (product id and name is sample below), selecting only entries of certain rank. In sample below you would select all entries with second biggest ID per category.

CREATE TABLE  #test ( id int, ProductName VARCHAR(25) )
insert into #test
select 1, 'Apple' union all
select 2, 'Apple' union all
select 5, 'Apple' union all
select 3, 'Orange' union all
select 4, 'Orange' union all
select 10, 'Orange'

SELECT * FROM #test

SELECT maxID FROM
(
    SELECT MAX(id) AS maxID, ProductName AS nn, RANK() OVER (PARTITION BY ProductName ORDER BY id DESC) AS MyRank
    FROM #test
    GROUP BY id, ProductName
) tmp
WHERE tmp.MyRank = 2

Get current username in SQL

SELECT PARSENAME(REPLACE(ORIGINAL_LOGIN(), '\', '.'), 1)

What is a size of DateTime type in C#?

What is a size of DateTime type in C#? - A trivial question, unexcitingly facing few obstacles. Self-explanatory code below describes how you can't get it and how you can (and yes, it's 8 bytes):

using System;

namespace DateTimeSizeExample
{
  public struct TypeSizeProxy<T>
  {
    public T PublicField;
  }

  public static class SizeCalculator
  {
    public static int SizeOf<T>()
    {
    try
    {
      return System.Runtime.InteropServices.Marshal.SizeOf(typeof(T));
    }
    catch (ArgumentException)
    {
      return System.Runtime.InteropServices.Marshal.SizeOf(new TypeSizeProxy<T>());
    }
  }

  public static int GetSize(this object obj)
  {
    return System.Runtime.InteropServices.Marshal.SizeOf(obj);
  }
}

internal class Program
{
private static void Main(string[] args)
{
// Error: 'System.DateTime' does not have a predefined size, therefore sizeof can only be used in an unsafe context
// (consider using System.Runtime.InteropServices.Marshal.SizeOf)
//int s1 = sizeof(DateTime);

// Run time Argument Exception: Type 'System.DateTime' cannot be marshaled as an unmanaged structure; no meaningful size or offset can be computed
//int s2 = System.Runtime.InteropServices.Marshal.SizeOf(typeof(DateTime));

int dateTimeSize = SizeCalculator.SizeOf<DateTime>(); // 8 bytes
}
}
}

Google Desktop Search as intranet search tool

Here is list of GDS proxies that could be used:

http://www.asabox.com/goolag/
http://sourceforge.net/projects/soogle/
http://www.projectcomputing.com/resources/desktopProxy/
(left as comment on my former post by anonymous good Samaritan).

See also: Turn Google Desktop into multisite search tool.

Dynamic casting in c#

var typesSubset = new[] { typeof(decimal), typeof(Int32), typeof(Int64) };

Type someDynamicType = typeof (int);

int someDynamicValue = 777;

if (typesSubset.Any(t => t == someDynamicType))

{

    object result = Expression.Lambda<Func<object>>(

        Expression.Convert(Expression.Add(

                               Expression.Constant(someDynamicType), Expression.Constant(someDynamicValue)),

                           typeof (object))).Compile()();

 

    Console.WriteLine(result); // 777

}

Pass xml to stored procedure

In C#:

1 // create XML string     2 var dataToPass = new StringBuilder("<RowsOfNumbers>");     3 dataToPass.AppendFormat("<id>{0}</id>", 1);     4 dataToPass.AppendFormat("<id>{0}</id>", 2);     5 dataToPass.AppendFormat("<id>{0}</id>", 3);     6 dataToPass.Append("</RowsOfNumbers>");     7      8 // call SP     9 myDB.ExecuteStoredProcedure("GetXmlData", new object[] { dataToPass.ToString() });

IN SQL:

CREATE PROCEDURE [dbo].[GetXmlData]    (@dataIdsXml XML) AS     DECLARE @dataIdsTable TABLE (ID int)       INSERT INTO @dataIdsTable (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')     FROM @dataIdsXml.nodes('/RowsOfNumbers/id') as ParamValues(ID)       SELECT [ID] FROM @dataIdsTable WHERE [ID]=1 GO

Workforce turnover around me (LinkedIn stats)

Following to LinkedIn data 57 out of my 382 contacts changed jobs in 2010.
This is 15%. Probably actual rate is a bit higher since in some cases this info may be not updated in LinkedIn.