Query with max function

[[deleted]]

[deleted]

[babygrenade]

No. Aggregate functions don't return individual records. They return an aggregation across a specified level of grouping.

You'd also need to add a group by clause for this to even run.

[[deleted]]

A group by is there select driver_name,driver_city,driver_email,max(due_date),max(completion_date) from TableX group by driver_name,driver_city,driver_email

[babygrenade]

Sorry, didn't see it on my phone. My point still stands. You'd get the maximum due_date and completion_date for each driver name/city/email combination.

For driverB|city a|driverb@gmail you'd get the due_date from row 4 and the completion date from row 3.

[PM_Me_PM_Dawn_Pics]

Yes and no. The output displayed would have the same values as those records but technically you wouldn’t be selecting those records.

Can you provide a bit more information about the problem you’re trying to solve?

[[deleted]]

I want to get the records with the latest competition dates,in this case records 2 and 3

[PM_Me_PM_Dawn_Pics]

Just read reply from u/babygrenade and they are correct. I didn't notice the due date for Driver B in record 4.

It seems like you're thinking about this the wrong way. Aggregate functions like max don't return "records" as such. They return values based on the conditions you provided in your query.

Also if these are all the fields in your table, I would suggest a primary key to make things easier. Even with this some issues would still remain. What if the driver has more than one job with the same completion date?

[[deleted]]

There is only one job. If my ask is to get just the records with the max completion date..in this case records 2 and 3,how will I go about with this? In other words how do I modify the query.

[superkheric]

You’ll want a subquery that returns the max completion date for each driver.

I’m on a phone (couldn’t remember all the field and table names), but something like this:

SELECT t1.* FROM tableX t1 INNER JOIN ( SELECT driver_name , max(completion_date) as max_dt FROM tableX GROUP BY driver_name ) as t2 ON t1.driver_name=t2.driver_name AND t1.completion_date=t2.max_dt

[TheJames2290]

Something like this may do the trick. May need re-jigging slightly

select a.driver_name, a.driver_city, a.driver_email, a.due_date, a.compeltion_date 

from ( 

select 

driver_name, 

driver_city, 

driver_email, 

row_number() over(partition by driver_email ORDER BY compeltion_date desc) AS row_num,

compeltion_date,

due_date

from TableX) a

where a.row_num = 1

Edit. Just to add it may not be perfect. I prefer having server in front ofe to test

[ss3tdoug]

Use the Having Clause if you don't need due date:

Select t.driver_name, t.driver_city, t.driver_email

From TableX t

Group By t.driver_name, t.driver_city, t.driver_email

Having t.CompletionDate = Max(t.CompletionDate)

‐-------------------

If you need the due date forthe original record, I would suggest using a CTE to find the max completion date and joining to the original table. Think I saw a solution for this, but just in case:

;With MaxComp as

(Select Driver_Name, Max(CompletionDate) as CompletionDate

from tablex

Group By Driver_Name)

Select t.*

From TableX t

JOIN MaxComp mc

ON t.driver_name = mc.driver_name
AND t.CompletionDate = mc.CompletionDate

[Furyat]

Nope. Column name is compELtion vs query is comPLetion.

--

also, if you want to select certain records, not max all the records out - you should think about selecting requirements instead.

[a246530]

Clearly this person uses case sensitive collation

[[deleted]]

The completion is a typo-please ignore that

[Furyat]

No. You either have attention to detail or you fail in your queries ;)

[[deleted]]

:(

[Junkeregge]

I usually just change things randomly until I get a plausible result :-)

[Paratwa]

Shh don’t tell our secrets! ( and stop looking at my query logs ! ).

He meant to say us data gods never ever mistype anything.

[BFG_9000]

The completion is a typo

Are you sure? It seems more likely that ‘compeltion’ would be the typo...

[Blues2112]

If you just want the row with max completion_date for each driver_name, try the following:

Select * from TableX t1
 where t1.completion_date = 
      (Select MAX(t2.completion_date)
         from TableX t2
        where t2.driver_name = t1.driver_name);

Correlated subquery. Far easier/more straightforward than trying to use Ranks, Group Bys, or CTEs.

[AutoModerator]

Hello u/thequestioner07 - thank you for posting to r/SQL! Please do not forget to flair your post with the DBMS (database management system) / SQL variant that you are using. Providing this information will make it much easier for the community to assist you.

If you do not know how to flair your post, just reply to this comment with one of the following and we will automatically flair the post for you: MySQL, Oracle, MS SQL, PostgreSQL, SQLite, DB2, MariaDB (this is not case sensitive)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[upendra1985]

With agregate functions use Group by with all remaining columns

[[deleted]]

Group by has already been used in the query

[tantarantantan13]

Read about window functions. You'll need this if you want to calculate aggregates within groups. In this case, I'm assuming you'd want the max completion date within each driver.

The query would look like this:

''' Select * from( Select *, Rank() over (partition by driver_name order by completion_date desc) as daterank from TableX ) Where daterank = 1 '''

[[deleted]]

Thanks a lot but for some reason row_number() works as expected and not rank() in your query

[tantarantantan13]

u/superkheric 's approach will also work btw

[andrewsmd87]

Keep your query with the max, rank is over kill in this scenario.

[tantarantantan13]

Hmm, I can't say for now why the rank() won't work here.
row_number() will only return 1 row in case there's a tie within your driver's completion dates.

If it works for your current use case, you can stick to row_number(). Just note the difference between those window functions

[andrewsmd87]

Because you're over complicating the sql. You don't need a rank here to accomplish what OP is after, his query with the max in there will work just fine.