How to find the second best grade of a student?

How to find the second best grade of the student? (If there's no second grade, just return the first one)

create table student_grades
(
student varchar(100),
grade int
);

insert into student_grades values 
('john',70),
('john',80),
('john',90),
('john',100);


insert into student_grades values
('paul',20),
('paul',10),

('paul',50),
('paul',30);


insert into student_grades values
('george',40);

Desired output:

 student | grade 
---------+-------
 george  |    40
 john    |    90
 paul    |    30
(3 rows)

Here's the code:

with ranking as
(
 select student, grade, rank() over(partition by student order by grade DESC) as place
 from 
 student_grades
)
select student, grade
from
ranking x
where 
exists (
  select null 
  from ranking
  where place <= 2
   and student = x.student 
  group by student
  having max(place) = x.place
 )

The following is shorter(works on Postgresql, Oracle) and simpler to read than above:

with ranking as
(
 select student, grade, rank() over(partition by student order by grade DESC) as place
 from 
 student_grades
)
select student, grade
from
ranking 
where (student, place) in
 (
  select student, max(place) 
  from ranking
  where place <= 2
  group by student  
 )

If only Postgres allows LIMIT clause inside an aggregation and made array_max a built-in function, this could be the shortest code:

select student, array_max( array_agg(grade order by grade desc limit 2) )
from 
student_grades
group by student;

Postgresql LAG windowing function

Given this:

create table t
(
ts timestamp not null,
code int not null
);

insert into t values
('2011-01-13 10:00:00', 5),
('2011-01-13 10:10:00', 5),
('2011-01-13 10:20:00', 5),
('2011-01-13 10:30:00', 5),
('2011-01-13 10:40:00', 0),
('2011-01-13 10:50:00', 1),
('2011-01-13 11:00:00', 1),
('2011-01-13 11:10:00', 1),
('2011-01-13 11:20:00', 0),
('2011-01-13 11:30:00', 5),
('2011-01-13 11:40:00', 5),
('2011-01-13 11:50:00', 3),
('2011-01-13 12:00:00', 3),
('2011-01-13 12:10:00', 3);

An stackoverflow user asked:

How can I select the first date of each set of identical numbers, so I end up with this:
2011-01-13 10:00:00, 5
2011-01-13 10:40:00, 0
2011-01-13 10:50:00, 1
2011-01-13 11:20:00, 0
2011-01-13 11:30:00, 5
2011-01-13 11:50:00, 3

I answered:

with sequencer as 
(
SELECT ROW_NUMBER() OVER(ORDER BY ts) seq, ts, Code
FROM T
)
select a.ts, a.Code
from sequencer a 
left join sequencer b on b.seq + 1 = a.seq 
where b.code IS DISTINCT FROM a.code;

But I'm not giving the problem a first-class treatment it deserves, I noticed somebody gave an answer that uses lag function, albeit he/she uses where prevcode <> code or prevcode is null

with r as
(
select ts, code, lag(code,1,null) over(order by ts) as prevcode
from t
)
select ts, code 
from r
where prevcode is distinct from code

Hmm.. :-) that code is very elegant, sleek and performant

http://www.postgresql.org/docs/8.4/static/functions-window.html

My stackoverflow answer on: Check if string is a rotation of other string

Found a curious puzzle on stackoverflow, how to check if the string is a rotation of another string.

My provided answer uses the modulo approach.  Hats off to the voted answer(uses concatenation approach), it's the best, I think even Jon Skeet was impressed :-)

Updating a Data Source with a Dataset

my answer on stackoverflow question Updating a Data Source with a Dataset:

[WebMethod]
public bool SecureUpdateDataSet(DataSet delta)
{

     string connStr = WebConfigurationManager.ConnectionStrings["Employees"].ConnectionString;

     using(var conn = new SqlConnection(connStr))
     {
        conn.Open();

        string sql = "select * from tab1 where 1 = 0";

        var da = new SqlDataAdapter(sql, conn);

        var builder = new SqlCommandBuilder(ad);

        da.InsertCommand = builder.GetInsertCommand();
        da.UpdateCommand = builder.GetUpdateCommand();
        da.DeleteCommand = builder.GetDeleteCommand();

        da.Update(delta);

        return true;

    }
    return false;
}