 Hello, Welcome to SSUnitech Social Decide and this is continuation of SQL Server interview questions and answers. So, today we are going to discuss one more interview question. So, how we can write the query for getting the median? So, what is this question? Let's move to next slide to understand about the question. So, we have input table that would have five columns. First is ID, city, state, Latin and long w. So, here we just want to write a query for getting the median of Latin and round it to a scale of four decimal places. So, here we can see we are having a column of Latin and we want to get the median of this. So, what is median? So, the median is the value separating the higher part from the lower part of the data sample. So, in this example as we can see the median of this Latin is 1.7456. So, how we can write the query for getting the output? So, before going to SSMS, let's go to on excel to understand about the logic how we can write the query. So, here as we can see we are having these five values 1, 2, 3, 4, 5 and the median is 3 as we can see over here. So, how we can calculate by using logic? So, first of all we need to get the, so here we need to get mid value then previous value and next value. So, for that here we will get 2, 3 and 4. Then we have to use the average function and we can put the average for this 2, 3 and 4. So, for that we are getting 3. So, this is the logic for calculating the median. So, let's move to sicko server management studio to write the query for getting this output. So, here as we can see first of all I would like to create the table. So, we can execute this command to create the table. So, command completed successfully. So, table is created. Next, I just want to insert record on this table. So, we can execute this insert statement. So, here we can see 4 rows affected. So, data is successfully inserted into the table. Now, we can write the select statement to check the data. So, select a stick from the station table and let me execute this query and here we can see we are having this data. So, how we can get the mid value? So, for that we have to use the row number. So, this is the row number. Then we have to use the over clause. Then we can use order by clause and in this order by clause that should be on let n and this time we want to get in decreasing order and we can put as decreasing Latin. Next, we just want to calculate the increasing order this row number. So, we can use row number and here we can use the over clause. Then we can use the order by. So, this is the order by and this is again for Latin and this time for ascending order. Now, we can use ascending order for this Latin. Now, we can execute the query. So, here we can see we are having 1 2 3 4 and 4 3 2 1. So, what would be the logic? Logics would be very simple. If this ascending value is equals to decreasing order or if ascending value plus decreasing order 1 minus decreasing order 1 then we want those values and put those values into the aggregate. So, how we can? So, this is very simple and we can use a derived table and let me call this as A. Here we can write select a stick from this A table. Here we can write where ascending Latin in descending comma descending plus 1 comma descending minus 1. Now, we can execute this query. So, it should be going to return 2 rows only because here we are having even number of rows. If we will have odd number of rows, so number of rows should be 3. Now, here we just want to use average function. So, we can use average then we can use Latin. Now, let me execute and check the output. So, this is the median 1.74 double 5 75 double 0, but this is too many decimal places. So, we just want to get up to 4 decimal places. So, we can use cast then numeric and only 4 decimal places. Now, let me execute and we are getting 1.7456 and here 1.7456. I hope you have understand how we can calculate the median. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much for watching.