Sunday, November 23, 2008

Learning SQL with Ganesh - Part 1 of ?


I found (and shared) this desperate help request yesterday on my google reader - I felt a burning desire to help the poor guy and I immediately followed the link to StackOverflow, but folks over there already edited the heck out of the original question.
Anyway this is what I'do to find AND delete duplicate rows on a table (with Identity):

DELETE
FROM MyTable
WHERE ColumnID NOT IN
( SELECT MIN(ColumnID)
FROM MyTable
GROUP BY Column1, Column2, ..., ColumnN )
If you don't have an identity on the table you can add it then drop it when you're done.

Anyway - I obviously up-modded the guy and went ahead to have look at other questions from him and - guess what - I found a pair of pearls (the previous one was pretty OK besides the original title):

Probably weirdest SQL questions ever asked, but anyway, even if I HATE SQL, I tried to find answers anyway ('cause that's human nature - I guess).

To get the top 10 rows without sorting I'd probably do some rocket science:
select top 10 *
from MyTable
-- duh!
but then, if random order is required (?), maybe something like:
select top 10 *
from MyTable
order by NEWID()
To get the last 5 rows without sorting I'd rather go with:
select top 5 *
from MyTable
order by ColumnID desc

But what if you don't have an ID? What does without sorting mean? In doubt, I'd probably do something like the following:

select top 5 *
from ( select *
from MyTable
order by newId() desc)

Which is some crazy shit and without doubt doesn't make any sense.

kick it on DotNetKicks.com

2 comments:

Anonymous said...

I stumbled on this blog searching for something related to my efforts as lead technical architect for a small (but up and coming) consulting firm. As a Gen-X'r (the 90's we're it for me) with a liberal arts education, a pragmatic approach to technology is refreshing. Thanks for your contribution.

Johnny Idol said...

thanks - but now I am curious about what you were looking for in the first place