# SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

## Aggregates with the Over Clause

You have likely heard the business term “Market Share”. If your company is the biggest and has sold 15 million units in an industry that has sold a total of 50 million units then your company’s market share is 30% (15/50 = .30). Market share represents your number divide by the sum of all other numbers. In JProCo the biggest grant (Ben@Moretechnology.com) is \$41,000 and the total of all grants is \$193,700. Therefore the Ben grant is 21.6% of the whole set of grants for the company.

The two simple queries in the figure below show all the Grant table records and the sum of the grant amounts.

If we want to show the total amount next to every record of the table – or just one record of the table – SQL Server gives us the same error. It does not find the supporting aggregated language needed to support the SUM( ) aggregate function.

Adding the OVER( ) clause allows us to see the total amount next to each grant. We see 193,700 next to each record in the result set.

The sum of all 10 grants is \$193,700. Recall the largest single grant (007) is \$41,000. Doing the quick math in our head, we recognize \$41,000 is around 1/5 of ~\$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head. We want the new column to divide each grant amount by \$193,700 (the total of all the grants).

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example:  the ratio 0.2116 represents a percentage of 21.16%. Multiplying a ratio by 100 will show the percentage. To finish, give the column a descriptive title, PercentOfTotal.

In today post we examined the basic over clause with an empty set of Parenthesis. The over clause actually have many variations which we will see in tomorrow’s post.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter5.0Setup.sql script from Volume 2.

## Question 10

You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

1. SELECT *, Min(HireDate) as StartDate FROM Employee
2. SELECT *, Max(HireDate) as StartDate FROM Employee
3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

### Rules:

Every day one winner will be announced from United States.
Every day one winner will be announced from India.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

## 130 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35”

1. Rene Alberto Castro Velasquez |

The correct answer is No. 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
Because all fields of the Employee table are shown, and an additional field called StartDate shows the first HireDate for all Employees.
OVER() gives the supporting aggregated language needed to support the MIN( ) aggregate function.
Rene Castro

Like

2. Reddy |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks.

Country – India

Like

3. You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Minimum of Hiredate will be first hire date. And since there is no group by clause, 1) is invalid. 3) with over() clause will put the earliest hire date in each row.

Leo Pius
USA

Like

4. 3. Select lowest value from HireDate to be displayed next to each row

Dan Q
United States

Like

5. Correct option is 3.
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

New Delhi

Like

6. a |

Explanation:
To show all fields of the table we will use the following query:
SELECT * FROM Employee

Since we want to display 1 more field called StartDate which will have the value of the first HireDate for all Employees we need to add Min(HireDate) OVER() as StartDate in the query

Correct Query is :
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country of Residence:
United States

Like

7. Sudhir Chawla |

Query 1 can’t be used because there is no GROUPBY clause and to use Aggregate methods Group by need to be used.

Other queries 2 or 4 used MAX and we need Min value, so its incorrect.

Thanks for the wonderful question.

Regards,

Sudhir
New Delhi, India

Like

8. Sumit |

The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

as we want to show first hiredate, which can be calculated using MIN() function
and Over() clause will print it for all the records

Sumit
India

Like

9. Question 10

You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

Wrong:Gives an Error as Aggregate function is used
1.SELECT *, Min(HireDate) as StartDate FROM Employee

Wrong:Gives an Error as Aggregate function is used
2.SELECT *, Max(HireDate) as StartDate FROM Employee

Correct:As over clause is used.Executes Correctly.
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Wrong:Executes correctly but uses Max date which gives recently joined Employee’s date
4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

Thanks for the Post :-)

Country :India

Like

1.SELECT *, Min(HireDate) as StartDate FROM Employee

Like

11. Hardik Doshi |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country: India

Like

12. Pratik Raval |

Correct Ans is option 3.

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Pratik Raval
India

Like

13. Keval |

Option 3 is correct

INDIA

Like

14. Dips |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

–> As Min(HireDate) shows the First Hire date for all employees as a new column ‘StartDate’.

Thanks,
Dips
INDIA [Noida]

Like

15. Option 3 is the right answer.

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Neelesh
India

Like

16. P.Pranav Kumar |

The Correct option is 3
i.e;
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

from,

Like

The correct answer is option 3 that is

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

As here we want first i.e. minimum hire date from all employee for for using MIN() function without using any group we want to add OVER() also.

INDIA

Like

18. The correct answer would be

3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Sandipak
Location: Minneapolis, USA

Like

19. Kewal Khanna |

Option 3 is correct

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Min(Hiredate) will show the first hiredate exist in the employee table, OVER() will help to remove the aggregate function with group by error.

India

Like

20. Mehul |

Since the first date is required so Min and among all data is required so Over().

Mehul Gardi
India

Like

First date is required so MIN and among all data so OVER()

Like

22. this should be the answer from the list of choices:

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Ritesh (India)

Like

23. Correct Answer option is 3

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Regards
india

Like

24. Narendra |

Hi Pinal,
None of the options seems to be Correct Answer for this question.

i think the option – 3 will works for this question, but it is returning the Minimum date from the HireDate column as the StartDate for all employees.
I think this is not Correct Answer.
Please Correct me if i am wrong.

Thanks,
Narendra(India).

Like

25. Mohd Thoufeek |

The Correct Answer is : 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Like

Explanation:
* Select *, will returns all Employee records

* Min(HireDate) OVER() as StartDate, returns Min(HireDate) i.e. First Date
OVER() which is according to employee id.

Like

27. ghanshyam |

3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

ghanshyam
bangalore

Like

28. GurjitSingh |

Option 3 is the correct answer

3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

City : Baroda
Country: India

Thanks
GurjitSingh

Like

29. Malay Shah |

The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

as we want to show first hiredate, which can be calculated using MIN() function
and Over() clause will print it for all the records

from:
Malay shah
Country:India

Like

correct option

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Because min() shows the minimum value of the column provided. if there is no aggregation provided so we will use OVer()

Country-India

Like

31. Hi,
Correct Ans is
3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Min Value give u first date Hiredate of Employee and Over() give u from hiredate of employee.

Country: India

Like

32. Shilpa |

correct Option is 3 .

Minimum of Hiredate will be first hire date,as there is no group by clause, 1) is invalid. 3) with over() clause will put the earliest hire date in each row.

Shilpa
India

Like

33. Brijesh |

The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

INDIA
Brijesh

Like

34. Correct answer: #3 (SELECT *, Min(HireDate) OVER() as StartDate FROM Employee)

Country of residence: India

I would like to mention to all readers that the OVER clause is magical, and once you start using it, you get addicted to it :)

Like

35. Correct Answer is Option 3:

OVER gives us ability to get minimum HireDate along with other columns.
Option 1 and 2, are not going to work, as we need all aggregate values
Option 4 is giving us LATEST HireDate

Country: INDIA

Like

1. SELECT *, Min(HireDate) as StartDate FROM Employee
– This query give an error as (invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause..)
2. SELECT *, Max(HireDate) as StartDate FROM Employee
– This query give an error as (invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause..)
3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
– This query executes successfully.
4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
– This query executes successfully, but it results as the maximum(latest) date of the employee joined.

Like

37. Rupesh Kumar Mishra |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Like

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country:India

Like

39. John |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

As SQl Server does not find the supporting aggregated language needed to support the aggregate function we have to use OVER() to make things understandable to it .

USA

Like

40. Anish Shenoy.P |

Hi Sir,

The correct answer is option no 3.

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

This option lists out all the employee related information for all the employees and The new column “StartDate” will have the first hire date from the table against each of the employee rows.

1 and 2 option will fail to execute with a error saying
“the first column of the from clause table is invalid in the select list because it is not contained in either an aggregate function or group by clause.”

4th option will give out the latest hire date from the table against each of the employee rows.

So the correct answer is 3 option.

P.Anish Shenoy
INDIA,Bangalore,Karnataka.

Like

41. Correct option is 3:

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Vairnder Sandhu (India)

Like

42. Partha Pratim Dinda |

Ans is 3

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Partha
India

Like

43. Corrent anwer is : 3

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

INDIA

Like

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Because,

Option 1 give a error because it is not contained in either an aggregate function or the GROUP BY clause.

Option 2 also give a error as option 1 and moreover it is MAX() function, it will not retrieve joining date.

Option 4 give a MAX date from HireDate column so it will not retrieve joining date.

Thanks,

Country:India

Like

45. Fazal Vahora |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country: India

Thanks & Regards,
Fazal

Like

46. Vipin |

Because we want all the records which are fetched by Select * and Start Date which is fetched by min(Hire Date) but with over()

Country: India

Like

47. Hello all together!

Option 1 and 2 does not work because of the lack of the over() clause. You will get an error (due to missing group by statement).
Option 4 does work but will return the last hireDate. But that does not match the question since it should be the first hireDate.
Option 3. gives the desired result(s).

Best wishes,

Michael Mikic
from Germany

Like

48. Paramita Banerjee |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Paramita Banerjee
country:India

Like

49. Correct answer :- option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Min will ensure lowest hire date and Over() will display against each row.

Thanks
Vivek Srivastava

Country: India

Like

50. sachin kulshreshtha |

Answer is : SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country : India

Like

51. Hi,
“SELECT *, Min(HireDate) OVER() as StartDate FROM Employee”
So Option 3 is correct.

Thanks…
Rajneesh Verma
(INDIA)

Like

My answer is option no. 3 because, first two option use aggregate function but not use Group by clause & in fourth option there is Max function is use so it returns with maximum date value of last column. My Country of residence is India.

Like

53. Correct answer is option 3.

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

(Sale, Nigeria)

Like

54. Somnath |

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Min Value give u first date Hiredate of Employee and Over() give u from hiredate of employee.

Somnath Desai

India

Like

55. Correct Ans : 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

If we don’t use Over clause than it will give that group by error as mentioned in your post and for Start Date we have to use Min() function.

Ishan Shah,
Gandhinagar,
India

Like

56. sikha rani mahapatro |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country: India(sikha)

Like

57. Correct answer is option 3.

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Jankhana,
India

Like

58. Correct Answer is Option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

MIN(HireDate) will give first hire date among all employees and OVER() clause will provide first hire date from hire dates of all employees.

COUNTRY – INDIA (Gujarat)

Like

59. Shree |

Option 3

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks

Shree

Bangalore India

Like

60. The correct answer is option 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee.

Nikhil Mahajan
India

Like

61. Vishal Shah |

Query 1 gives error as no GROUPBY clause is there. To use Aggregate methods Group by need to be used.
Query 2 and Query 4 used MAX function.
Query 3 gives minimum Startdate for all employees.

Like

62. Vishal Shah |

country : India

Like

63. Deb |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Both 2 and 4 are incorrect because they are taking the last start date, not the first and #1 won’t work because there is no group by.

USA

Like

64. Bill Pepping |

Hi Pinal,

Challenge:
Question 10
You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

1.SELECT *, Min(HireDate) as StartDate FROM Employee
2.SELECT *, Max(HireDate) as StartDate FROM Employee
3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

The correct answer is #3: SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Explanation:
Items #1 and #2 forgot to include the Over clause. Items 2 and 4 are looking for the most recent HireDate. Item #3 is retrieving the earliest HireDate, using the Over clause.

Country:
United States

Thanks for the knowledge!

Regards,

Bill Pepping

Like

65. Mike Michalicek |

USA

Mike Michalicek

Like

66. sravan |

correct option is 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

i’m from india

Like

67. Ritesh Choksi |

India

Like

68. Gordon Kane |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Gordon Kane
Allen TX
USA

Like

69. Leonardo Guerrero |

Option 1 and 2 is not correct because will get invelid delection if you run that query.
OPtion 3 and 4 work, because you can get and answer, but….
option 4 show you the newest date not the first one as qhe question needs .
So, the correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Leonardo

Country: Chile

Like

70. Shirish |

Correct option is 3.

Shirish Kulkarni

Country: INDIA

Like

71. Yasodha |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

We need to select first hire date, option 2 and 4 used the MAX() Aggregates function is used so both are wrong.

Option 1 doesn’t use any groupby clause…So option 3 is the correct answer.

Yasodha.N(India)

Like

72. Chetan |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

USA

Like

73. Srinivas Reddy |

Country: USA

Like

74. 3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

USA

Like

75. Naomi |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
and will get the earliest Hire Date in the whole company. We can also add

case when HireDate = MIN(HireDate) OVER() then ‘First Employee’ else ‘Later addition’ end as [Status]
and therefore determine the founders of the company.

I am from USA

Like

76. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Rajesh Mohanrangan
From India

Like

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Rajesh Mohanrangan
From India

Like

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Kulwant kumar
Delhi India

Like

78. 3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Divesh
INDIA

Like

79. Jagdish Prajapati |

Hi all Option 3 is correct

INDIA

Like

80. Shekhar Gurav. |

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Shekhar Gurav.
Country : INDIA

Like

81. sandhya |

option 3 is the correct one.
as first 2 queries are not valid.
the min(hiredate) over() gives the first hire date of all the dates for that employee.
which is the answer u expect .

Boston USA

Like

82. Correct Answer is Option 3:

OVER() gives us ability to use aggregate columns along with other table columns.
Min() will give us the minimum value of the column.

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country: INDIA

Like

Query in option 1 and 2 will give error. And Query in option 4 gives the last employees hiredate. So the Correct answer is 3.

Thanks,
India

Like

84. Dnyanesh Deshpande |

1.SELECT *, Min(HireDate) as StartDate FROM Employee
Will generate error as we are not using Over().

2.SELECT *, Max(HireDate) as StartDate FROM Employee
Will generate error as we are not using Over().

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
This statement will generate correct result.

4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
This Statement will not generate desired result because of Max().

Thanks
-Dnyanesh

Like

85. Dnyanesh Deshpande |

1.SELECT *, Min(HireDate) as StartDate FROM Employee
Will generate error as we are not using Over().

2.SELECT *, Max(HireDate) as StartDate FROM Employee
Will generate error as we are not using Over().

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
This statement will generate correct result.

4.SELECT *, Max(HireDate) OVER() as StartDate FROM Employee
This Statement will not generate desired result because of Max().

India

Thanks
-Dnyanesh

Like

Hi Pinal,

Correct Answer is – # 3

#4 doesn’t come in consideration as its looking for Max(HireDate) while we are looking for First hire date.

#1 and #2 will give an error..

Where #3 will give us as the requirement

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee is Correct Answer.

I’m from Chicago, USA

Like

87. rama chandra sahu |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country: India

Like

88. Option 3 is Correct.

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Option 1 and 2 will give error as Aggregate function is used without “group by”.

Max function in statement 4 will not give the correct result.

Yeou Sunn
India

Like

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Rahul Thapa

India

Like

90. kaushik thanki |

kaushik thanki

india.

Like

91. Kalyanasundaram.K |

Answer for today question – Option : 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Today blog is useful to know over clause in SQL

Like

92. Don |

The best way to show all fields of the Employee table with an additional field called StartDate that shows the first HireDate for all Employees would be to use option 3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee.

Country: United States

Like

93. Alagu Mano Sabari M |

Option : 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Chennai, India

Like

94. Anand Kumar |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country – India

Like

95. Hi Pinal Sir,

The correct answer for above question is Option No. 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Explanation:

OPTION 3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

In above query all the fields (columns) from the Employee table are selected, and a added field called StartDate shows the first HireDate for all Employees it is simple as we all know but then from your article you explained OVER() gives the supporting aggregated language needed to support the MIN( ) aggregate function and hence our problem is solved by this option.

WHY Other options are wrong.

OPTION 1) This option SELECT *, Min(HireDate) as StartDate FROM Employee error occurs as Aggregate function is used without “group by”.

OPTION 2) This SELECT *, Max(HireDate) as StartDate FROM Employee query generates error as Aggregate function is used without “group by”.

OPTION 4)SELECT *, Max(HireDate) OVER() as StartDate FROM Employee here it is giving max but we want minimum so wrong option.

DILIP KUMAR JENA
Country : INDIA

Like

96. The correct option is #3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Option 1 and 2 would generate errors. Aggregate functions used in this context would need a GROUP BY statement.

Option 4 grabs the Max(HireDate) which would be the last, not the first, hire date of all of the employees.

Like

97. A. Arul Prakash |

3) SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Minimum of Hiredate will be first hire date. And since there is no group by clause, 1) is invalid. 3) with over() clause will put the earliest hire date in each row.

Country : USA

Like

Hola, interesante proyecto que sigo hace un par de años.

Aunque no corresponda mi participación, ya que, no vivo en India o EEUU, la respuesta correcta es la número 3.

Determina la menor fecha de contratación y la asocia a todas las ocurrencias que existen en la tabla..

(Option 3: SELECT *, Min(HireDate) OVER() as StartDate FROM Employee)

Vivo en Santiago de Chile,

Like

99. Thomas Riehle |

If you do not use OVER() you will get an error in your query. Also, you want the FIRST hire date, so you do not want to use MAX(), which leaves only option #3.

Country: United States

Like

100. Krishnaraj |

Like

101. Srikanth Nallamothu |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Like

102. Nikhildas |

The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Cochin,INDIA

Like

103. Ritesh Choksi |

India

Like

104. Prasanna kumar.D |

Answer for today question – Option : 3
SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Today blog is useful to know over clause in SQL

Like

105. Eric |

Hello

Love this series. The correct answer is #3, SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks
Eric
USA

Like

106. Francisco Gil |

The correct answer is the #3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Regards,
Francisco
Miami

Like

107. masood abedin |

Option 3 because OVER() clause allows the partitioning and ordering. of the rowset.

Like

108. masood abedin |

masood abedin,I am from usa

Like

109. Q 10) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

A.) Because there is no supporting language for the aggregated field both (1) and (2) will not be correct. Because Max (HireDate) will return the greatest or most recent HireDate (4) is also incorrect. The correct code is (3) which uses the Min aggregate function and the OVER ( ) clause as supporting language.

Winner from USA: levpius

Winner from India: Deva Rajan

I thank you all for participating here. The permanent record of this update is posted on facebook page.

Like

110. Ranjit kumar |

3.SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Country:India

Like

• You’ve really captured all the esetsnilas in this subject area, haven’t you?

Like

111. joseph |

select *,HIREDATE -max(hiredate) over() as diff from emp2

Like

112. Uday Bhoopalam |

Correct answer is #3. I am coming across over() for the first time

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Uday
USA

Like

113. Wayne |

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

Thanks,
Wayne (USA(

Like

114. Krishnat Patil |

The correct answer is option 3

SELECT *, Min(HireDate) OVER() as StartDate FROM Employee

INDIA

Like