MXimize by Carlos Gallupa, thoughts about and experiments with coldfusion, css, sql, components and javascript

Getting the closest locations based on Latitude / Longitude

7190 viewed | 4 opinion(s)  | Tjarko @ 23:43 cet

How to Remove, Delete double records but still have the values intact.

First we get all the double records. In this case I have a table called "test" with 2 columns "tst_id char(26) DEFAULT newid(),tst_name varchar(50)".

Secondly we use the GROUP attribute in the <cfquery> tag to only get SINGLE records. In this query we declare a temporary table (MSSQL - memory only) and we insert the SINGLE records in that table

then we DELETE all the double items and after that insert all the single items back into the original table from the temporary table.

Leaves you with all the double items removed!! BUT BE CAREFULL, this method doesn't take into account that records may have a relation with other tables in the database...

<cfquery>
SELECT  tst_id,tst_name
FROM test
WHERE UPPER(tst_name) IN (
  SELECT UPPER(tst_name) as tst_name
  FROM test
  GROUP BY tst_name
  HAVING (COUNT(UPPER(tst_name)) > 1)
)
ORDER BY tst_name
</cfquery>

<cfsavecontent variable="sqlStmt">
  DECLARE @tmp TABLE (tst_id char(36),tst_name varchar(50))

  <!--- Only insert single items of the double records --->
  <cfoutput query="qDouble" group="tst_name">
    INSERT INTO @tmp VALUES ('#tst_id#','#tst_name#')
  </cfoutput>

  <!--- Delete ALL double records --->
  DELETE
  FROM test
  WHERE UPPER(tst_name) IN (
    SELECT UPPER(tst_name) as tst_name
    FROM test
    GROUP BY tst_name
    HAVING (COUNT(UPPER(tst_name)) > 1)
  )
  
  <!--- Insert the single records back in the database --->
  INSERT INTO test (tst_id,tst_name)
  SELECT tst_id,tst_name FROM @tmp
</cfsavecontent>

<!--- Execute the query --->
<cfquery>
  #PreserveSingleQuotes(sqlStmt)#
</cfquery>

10840 viewed | 2 opinion(s)  | Tjarko @ 10:07 cet

How to find duplicate values in a table?

With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the table you need to search.

In your recordset you will see the tablefield and how many times it is found as a duplicate.

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!!

SELECT *
FROM table
WHERE tablefield IN (
 SELECT tablefield
 FROM table
 GROUP BY tablefield 
 HAVING (COUNT(tablefield ) > 1)
)

To go even further in the process and DELETE every double record we could do something like make a temporary table, insert the double records, delete it from the original table and insert the saved single records from the temporary table.

186789 viewed | 29 opinion(s)  | Tjarko @ 23:56 cet

How do I randomly show a query record?

The easiest way to do this is by the "ORDER BY newid()" statement within your SQL statement. Drawback is that you need SQL server with this.

SELECT TOP 1 *
FROM table
ORDER BY newid()

The query above will give you a randomly choosen record each time.

Another way of doing this is by randomly displaying a record from the queryset you have.. you can do that like this (if you use access or some other database)

<cfquery name="rs" datasource="#dsn#">
  SELECT *
  FROM table
</cfquery>

<cfset displayRow = randRange(1,rs.recordcount)>
<cfoutput query="rs" startrow="#displayRow#" maxrows="1">
  -- show your record
</cfoutput>

13098 viewed | 5 opinion(s)  | Tjarko @ 10:06 cet

Insert with a select

Sometimes you need to update a table from another table and what you can do then is SELECT all data.. loop over it with CF en INSERT the data into the other table....OR.... you can let the database sort it out.

INSERT INTO table1 (column1,column2,column3....)
SELECT column1,
    column2,
    'somevalue' AS column3,
    ....
FROM table2

6290 viewed | 2 opinion(s)  | Tjarko @ 14:59 cet

Multiple conditions in JOIN statement

You can put multiple conditions in your LEFT, RIGHT and INNER/OUTER joins if you want.. below a small example that will give you the idea.

SELECT tbl.id, tblo.naam
FROM sometable tbl
  LEFT JOIN someothertable tblo ON tblo.id = tbl.id
   AND tblo.function = tbl.function
ORDER BY ....

Didn't know this one.. could have saved me a lotta query's in the past :-(

62325 viewed | 11 opinion(s)  | Tjarko @ 0:00 cet