Where's Jakey?!?!

By billy


The CJ mascot appears to have gone walkabout this week! Oh well, let’s hope he makes an appearance next week....
 

  Jakey - What you looking at?!

SQL (TSQL)Tips & Tricks

By billy

Hi All,

Some great tips and tricks for SQL for ya'll.

DISTINCT & GROUP BY

http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

INNER JOIN - REMOVING DUPLICATES

(Within the INNER JOIN you can get the TOP 1 of a unique field and then use this to only return 1 from the koop up table, in this case Field1 and Field 2 can contain different values and therefore a DISTINCT will NOT work)

SELECT
TABLENAME.requiredField,
LOOKUPTABLENAME.Field1,
LOOKUPTABLENAME.Field2,
FROM TABLENAME
INNER JOIN LOOKUPTABLENAME ON LOOKUPTABLENAME.uniqueField = (SELECT TOP 1 uniqueField FROM LOOKUPTABLENAME WHERE LOOKUPTABLENAME .requiredField= TABLENAME.requiredField)
ORDER BY TABLENAME.requiredField

DUPLICATES

Also deleting duplicates ROWS from a table (need to :

DELETE FROM T1
FROM TABLENAME T1, TABLENAME T2
WHERE T1.dupField = T2.dupField
AND T1.uniqueField > T2.uniqueField

Hope they come in handy!

Laughing