Yussi Ariefiyono
sql
Finding Duplicates with SQL
Nov 27th
problem:
finding the duplicate within sql
solution:
provide table named subscriber, with elements: email
SELECT COUNT(DISTINCT email) AS NumberOfEmail, email FROM subscriber GROUP BY email HAVING (COUNT(email) > 1)
select NOT in top post sql query
Nov 27th
problem:
make some selection in database OTHER THEN top (10) post
solution:
provide, table named news with element: newsID, newsDate
SELECT * FROM News WHERE (newsID NOT IN (SELECT TOP (10) newsID FROM News AS News_1 ORDER BY newsDate DESC)) ORDER BY newsDate DESC
it will return all post except top 10 post
calculating age in sql
Aug 3rd
problem:
display age with sql command
solution:
sql command:
SELECT DATEDIFF(mm, dateOfBirth, GETDATE()) / 12 AS age
FROM someTable
it wil display age ^^V
birthday reminder list asp.net
Aug 3rd
problem:
displaying list of bday boy/girl in next (30/ according to your own choice ) days from the current date.
solution:
im using the sql to select the date of birth, for displaying, you can use whatever you want, like gridview, datalist etc.
hereby the sql command:
SELECT DateOfBirth
FROM someTablename
WHERE (DateOfBirth < GETDATE()) AND (DATEDIFF(d, GETDATE(), DATEADD(yy, DATEDIFF(yy, DateOfBirth, GETDATE()), DateOfBirth)) BETWEEN
0 AND 30)
it will return the list of date of birth from the current date.
for reference of the sql command:
http://msdn.microsoft.com/en-us/library/aa258269%28SQL.80%29.aspx
SELECT next, last row or “LIMIT-command” in MSSQL
May 26th
SELECT previous row or "LIMIT-command" in MSSQL
SELECT TOP (1) FROM table_name WHERE (ID< @ID)
ORDER BY ID DESC
SELECT next row or "LIMIT-command" in MSSQL
SELECT TOP (1) FROM table_name WHERE (ID> @ID)
ORDER BY ID
Thank also for the descussion