DEV Community: alokz diptim!

How to remove duplicates records in SQL (ROW_NUMBER, Partition, CTE) Part 1

alokz diptim! — Sun, 03 Sep 2023 02:39:25 +0000

I will begin by explaining the ROW_NUMBER function in SQL, which is used to assign sequential values to rows in a table.

The crucial concept here is the sequential increment applied to rows within a table. It's important to note that this function can be applied to temporary tables, tables without traditional IDs, or even tables with unique identifiers (UIDs).

Typically, ROW_NUMBER is a function that can be used without a parameter, so it can be declared as ROW_NUMBER().

The next crucial keyword is "OVER," which is used in conjunction with functions in SQL. It is required here to determine how the sets of rows in the table will be arranged. In most cases, it specify how we want to arrange the table.

Now, the OVER function requires two parameters:

OVER ([PARTITION BY value_expression] [order_by_clause])

However, our focus will be on the ORDER BY clause, with a mention of the PARTITION BY clause later.

In tables like this, the IDs are sequential, so we may not necessarily need the "row_number" for simple operations such as ordering the rows.

It can be waived in such cases but is typically used in more advanced scenarios.

However, tables with unique IDs like this require a Row_Number because they are not sequential. I can't even count them correctly if there are more than 2000 rows.

So, in order to perform further logic on this table, we may need to generate a Row_Number for it.

Our SQL table, which is named "Cars," has two columns: "Id" and "Name."

If we intend to fetch the rows, we would typically write:

SELECT * FROM Cars;

However, if we want to include the RowNumber, you can use the ROW_NUMBER() function in your query. Here's an example:

SELECT 
Id, Name,
ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
FROM Cars;

This query will return the rows from the "Cars" table along with a RowNumber column that assigns a unique number to each row based on the "Id" column's order.

Now, this gets a bit more interesting

I will be posting the next series shortly.

[algorithm] Multiple pointers pattern explained! (EPISODE 1)

alokz diptim! — Thu, 29 Apr 2021 22:28:22 +0000

This is part of my quick series on algorithm --- with a lot of images to buttress the point. There are many ways of searching through an array. The simplest and obvious is using a loop!

But the concern it raises is when the iterations are overused -- Loop inside a loop has an effect on how our program runs on a large scale of data. (N square time complexity)

For Loop
    For Loop

So there ought to be a better way of searching through an ordered list

Let's look at an example of an array { 2, 3, 7, 9, 17, 1, 5 } in which we need to find the two sums of the array element that gives us 10!

Pretty easy ei?

Looking at the component of the array,

7 + 3 and 9 + 1 will both give 10

First and foremost, we start with the zero-based index and search through the whole array list. In simple term, it is the summation of the zero-based element with every single element in the array list

DAMN! we didn't see it!

.......Moving to the next index........

Pretty much the summation of all the elements in the array with the first index and we didn't see it. We decided to shift the index by 1 and rescan through the array

Yes!! we found it.

But assuming the array has close to 1 Million elements then we need to scan through the array list, and move the start/correlation index by 1 and rescan through the one million. ~ Repeat.~ (That's time and resource poor performance)

 class Program
    {
        static void Main(string[] args)
        {
           int[] result = SumArray(new int[] { 2, 3, 7, 9, 17, 1, 5 }, 10);

            Console.WriteLine($"{result[0]}, {result[1]}");

            Console.ReadLine();
        }

        private static int[] SumArray(int[] array, int luckySum)
        {
            for (int i = 0; i < array.Length; i++) //Use this to get the index of each element
            {
                for (int j = i+1; j < array.Length; j++) //use this loop to scan through the array
                {
                    int firstVal = array[i];
                    int secondVal = array[j];

                    int sum = firstVal + secondVal;

                    if (sum == luckySum)
                        return new int [] { array[i], array[j]};
                }
            }

            return new int[] { };
        }
    }

So not efficient therefore we need to discard that approach. (unless you working with an unordered list)

Then give room for the multiple/double pointer approach

To be explained in the next episode.... stay tuned

What are SQL Indexes and how to create one? (For Beginners)

alokz diptim! — Mon, 06 Jan 2020 11:51:14 +0000

Indexes are lookup tables that when used speed up data retrival within a voluminous table.

I'll appreciate your feedback so I can improve on the sketches.

Where Javascript Coercion went wrong?

alokz diptim! — Wed, 02 Jan 2019 08:20:24 +0000

Coercion is unknowingly the conversion of a datatype to another with the use of the Javascript engine. One of the few surprises in Javascript. Now, this behaves differently for various operators and data types such as:

+ Operator
- Operator
Boolean
Equality signs
Arrays

It happens mostly because of the dynamic type declaration of javascript variable. The use of var.

With the use of other programming languages such as c#, If we make use of numbers and strings as data types, then, compile-time ought to show us the error before runtime.

So let’s dive in into each instance of Javascript coercion:

From practice, the + operator returns strings, so the Javascript coerce the operands to strings. Example: 3 + “90” will return 390
Negative sign acts as a number, and it coerces the operand to a number instead Example: 90 - “3” will return 87
Boolean, yes!
- The or/and logic gates
- Apple || “orange” returns Apple which is the first value for the or operator and also because it’s true.
- If it were (undefined| “”| 0| false) || “orange” then orange would be returned.
- So you can write a function such as this: Function Whatsup(nickname){ Nickname = nickname || “Stranger.”; console.log(“Hey ” + nickname); } Whatsup(); Since nickname would be undefined and then false, the console will print out “Hey Stranger.”
->> and for the “and” operator; It returns the second value because it’s always false;
Equality checks for values and types == checks for values, so coercion still occurs. If (44 == “44”) will return true but if the triple operator equality sign is used instead, it will return false. Ex:If (44 === “44”) returns false;
The Array will return Nan for the - operator Ex; [“Right Now”] - [“Later”] will return Nan(Not a Number) While [“Right Now”] + [“Later”] will concatenate and return RightNowLater as a string

Remember the + operator always return strings as their value.

So in an instance where we have:

console.log(1 < 4 < 3); it will return true instead of false because the javascript sees it as:
console.log(true < 3);
“true” would be coerced to 1 and when compared will be 1 < 3 which will be true.

Here are some of the weird behaviours of coercion.

They are best tackled with strict equality operator sign, ===

But then again, coercion may not only be with the comparison but also how javascript checks for a boolean value.
To check how Javascript coerce output, check out Number(“”), Number(), or Boolean(undefined) and see what it coerce to with the given results.
Ex: I can check,
boolean("0"); that will be coerce to false Boolean(undefined); will be coerce to false Number("3") will be coerce to 3; Number("Hello") will be coerce to NaN

Also checking the precedence matters as well.

Thanks and that’s all about Coercion.

If you love/like, please share/comment. Thanks!

Linear Search with c#

alokz diptim! — Fri, 03 Nov 2017 16:13:35 +0000

 class Program
    {

        static void Main(string[] args)
        {
            linearIndex(0);
            Console.ReadLine();
        }

        public static int linearIndex(int searchNumber)
        {

            int index = 0;
            int[] intArray = { 
        47,56,38,5,40,17,26,58,56,25,10,14,38,10,36,15,36,53,55,33,10,48,6,45,

       38,10,49,47,53,10,52,45,32,46,55,19,55,53,11,39,36,25,26,41,15,1,43,
       40,13,53,39,11,53,33,5,21,36,53,15,36};

         l1:   if (searchNumber == intArray[index])
            {
                Console.WriteLine("found at! : {0}", index);
                return index;
            }

            index ++;
            if (index < intArray.Length)
            {
                goto l1;
            }
            else
            {
                Console.WriteLine("Not found at any Index");
            }

            return 0;
        }

    }

The console app started from the main method and I passed in an int 0 to check against the arrayList,

Well, there is a comparison and as long as the index of the array is less than the array length, then we are good to continue the search.

If the option is maxed out then that means we didn't get to see the number.

Binary search using c#

alokz diptim! — Fri, 03 Nov 2017 09:35:21 +0000

I'll be breaking down the documentation if you need help.

    public static int BinarySearch(int arraySize, int targetNumber)
    {

        Random random = new Random();
        int[] figures = new int[arraySize];

        while (!figures.Contains(targetNumber))
        {
            for (int i = 0; i < figures.Length; i++)
            {
                figures[i] = random.Next(0, 15);
            }
        }

        //SORTING OUT THE ARRAY
        Array.Sort(figures);

        int minimum = 0;
        int maximum = figures.Length - 1;

        l1:

        int center = (maximum + minimum) / 2;

        if (figures[center] == targetNumber)
        {
            return center;
        }

        if (minimum > maximum)
        {
            Console.WriteLine("This kind of Array doesnt exist and this is false. Please add some element to the element. Thanks!");
        }

        if (figures[center] < targetNumber)
        {
            minimum = center + 1;
        }

        if (figures[center] > targetNumber)
        {
            maximum = center - 1;
        }

        if (figures[center] == targetNumber)
        {
            return center;
        }

        goto l1;

    }

    static void Main(string[] args)
    {
        Console.WriteLine(BinarySearch(10, 8));

        Console.WriteLine("The End");
        Console.Read();
    }
}

}