syntax question-maybe easy and light

Posted on 2004-11-03
Medium Priority
Last Modified: 2010-03-19
I am trying to get to run this...

Select   RegularRate, OTRate, max(effectivedate)  from AssignToLookup where

but it is giving me an error saying it is part of aggregrate function or something like that.

All I need to get is the highest date entered for that particular title.
Question by:zachvaldez
  • 2
LVL 11

Assisted Solution

rdrunner earned 80 total points
ID: 12483736
Remove the other 2 fields from your querry and it should work...

Select   max(effectivedate) Maxeffectivedate  from AssignToLookup where

If you want the other fields also you need to group by them or include them in aggregate functions also

Accepted Solution

svid earned 120 total points
ID: 12483825
If you need the other columns, as rdnummer pointed out,

Select   RegularRate, OTRate, max(effectivedate)  from AssignToLookup where
group by RegularRate, OTRate

Author Comment

ID: 12483852
Thanks, I have to split the points..
LVL 11

Expert Comment

ID: 12483900
If you group by the other fields then the result might not be what you expect...

For example if there are several records for one TitleID in the AssignToLookup table you wont get what you asked for... You will "only" get the "Highest effectivedate for each TitleID where all the fields ( RegularRate, OTRate) have the same value..

In an example this would mean....

 RegularRate, OTRate, effectivedate , (TitleID is same for all records so i will skip it here)
12, 20, 1.1.04
12, 20 , 1.9.03
13,30 , 1.5.04

You will revieve 2!!! results...
12, 20, 1.1.04
13,30 , 1.5.04


Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question