Recent Entries
I have a few useful sql scripts saved in a sql help folder on my computer and one of those queries includes a sql script that finds all duplicate records and then allows me to delete them.
One of the main advantages of using a common table expressions, CTE, is so that we don't have to bother using a temp table because we can query the CTE to compare the data we wish to check for duplicates. Let's look at an example:
SELECT Col1, Col2, COUNT(*) FROM TABLE1
GROUP BY Col1, Col2 HAVING COUNT(*) > 1
This query will return a list of records in which Col1 and Col2 are the same for more than one record. This is our list of duplicates we wish to remove. But how can we keep one record and delete the duplicate? It would be easier if we had a row number and have the record we wish to keep as always one. So let's try this using a CTE.
WITH CTE (COl1,Col2, Row)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY
COl1,Col2 ORDER BY Col1) AS Row
FROM TABLE1
)
SELECT * FROM CTE
When the above script is run we will see a list of records but the duplicate records will have a Row greater than 1. This allows us to easily remove the duplicate rows using the final script below.
WITH CTE (COl1,Col2, Row)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY
COl1,Col2 ORDER BY Col1) AS Row
FROM TABLE1
)
DELETE FROM CTE WHERE Row > 1
As you can see CTEs can be very useful in allowing us to quickly create a list of data that we can use in other queries as well as using the ROW_NUMBER() function to let us distinguish original from duplicate.
When showing some information in C# we can use the class variables within the aspx page itself similar to classic ASP.
First Name: <%= User.FirstName %>
Last Name: <%= User.LastName %>
Email Address: <%= User.EmailAddress %>
My issue is that when one of the variables is null, then we get a run-time error. So for example, if User.EmailAddress == null
then how can we use the variable in the aspx page? One way is to use the null coalescing operator (??) and substitue string.Empty in case User.EmailAddress is null.
First Name: <%= User.FirstName %>
Last Name: <%= User.LastName %>
Email Address: <% User.EmailAddress ?? string.empty %>
Now I'm not sure if there is a "better" way to handle this but in my case, this worked just fine.