Selecting the max date and group by

Question

0.00/5 (No votes)

See more:

Hi
I am currently trying to do a report and pull the data from SQL

The problem i am having is that i cant seem to get the information i require

I have a view called listStage.
this view represents 5stages(tables) in my db
my statement is

SQL

Select max(date),Product,stage from liststage group by product,stage

Problem with this is it doesnt just return the maximum date in the view, i just need it to show the maximum date,product and stage.

The query returns the maximum date at each stage.

How can i get it to show the just maximum date,with product and stage?

Posted 18-Feb-13 21:11pm

isi19

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2013-02-18T21:14:00

Solution 1

Try

SQL

SELECT Date, Product,stage FROM liststage 
WHERE Date = (SELECT MAX(Date) FROM liststage)

--OR

SELECT TOP 1 Date, Product,stage FROM liststage 
ORDER BY Date DESC

Posted 18-Feb-13 21:14pm

__TR__

Updated 18-Feb-13 21:17pm

v3

Comments

isi19 19-Feb-13 4:17am

Thanks , but I need to show more than the last record with the maxdate, i need to select all the products,the max date and the stage ,

See the products go to stages, and when they are completed at a stage they go to another stage,to track history of where the product is i created a view listStage,so now i want to view all the products whats the current stage bassed on the date it was completed

__TR__ 19-Feb-13 6:25am

I did not understand your requirement. If you are looking to get the Product,stage and the date it was completed the query posted in your question might be correct.
It would be better if you can post some sample data and let us know the issue you are facing with the query you have written.

Abhai Oza · Answer 2 · 2013-02-18T21:38:00

Solution 2

SELECT TOP 1 Date, Product,stage FROM liststage ORDER BY Date DESC

Posted 18-Feb-13 21:38pm

Abhai Oza

Khorshed Alam, Dhaka · Answer 3 · 2013-02-18T23:23:00

Solution 3

Is the 'date' column datatype is DATETIME if not try this one

SELECT MAX(CAST([date] AS DATETIME)) AS MaxDate,
       Product,
       stage
FROM   liststage
GROUP BY
       product,
       stage
ORDER BY
       MAX(CAST([date] AS DATETIME)) DESC

Hope this will work.

Posted 18-Feb-13 23:23pm

Khorshed Alam, Dhaka

Updated 18-Feb-13 23:31pm

v2

Comments

John Y. 19-Feb-13 9:18am

Does the view then have all the products with all its various dates and stages? And you just want to see the products at their most recent date/stage?

Maciej Los · Answer 4 · 2013-02-19T08:28:00

Solution 4

If want to get max date and max stage, try this:

SQL

SELECT [Product], MAX([date]), MAX([stage])
FROM liststage
ORDER BY [product]
GROUP BY [product]

Posted 19-Feb-13 8:28am

Maciej Los