How to get max date of a column along with its corresponding data from multiple columns

Question

0.00/5 (No votes)

See more:

I need to write a query to fetch data of item code and max date along with its corresponding column values lies in that row.

I tried below code and it gives mupltiple rows for each item code.I want only one row
for each itemcode along with max date and other column data.

any help would be greatfull

query output:

batch_No,bill_No,bill_Date,srno,item_Code
'GG','123','2019-08-19',39,'000365'
'AL','1217','2017-06-17',53,'000211'
'AL','123','2019-08-19',64,'000211'
'AL','12h55','2019-09-03',75,'000211'
'GG','1df4','2019-09-01',36,'000365'
'AL','123','2019-08-19',35,'000211'
'AL','123','2019-08-19',78,'000211'
'AL','123','2019-08-19',79,'000211'
'AL','123fz','2019-09-02',76,'000211'
'AL','123','2019-08-19',80,'000211'
'AL','123','2019-08-19',82,'000211'

now it might be only two item codes its just smaple data some times it may conatin 5 or 10 itemcodes please suggest consider this point

what i want from this query is just one row based on maximum date for itemcode

'GG','123','2019-08-19',39,'000365'

'AL','12h55','2019-09-03',75,'000211'

What I have tried:

select purdet.c_batch_no batch_No,purmst.c_bill_no bill_No,max(purmst.d_bill_date) bill_Date,purmst.n_srno srno,purdet.c_item_code item_Code
from pur_mst purmst
join pur_det purdet on purdet.c_br_code=purmst.c_br_code and purdet.c_year=purmst.c_year
and purmst.c_prefix=purdet.c_prefix and purdet.n_srno=purmst.n_srno
where purdet.c_item_code + '/' + purdet.c_batch_no in('000211/AL','000365/GG')
group by purdet.c_batch_no,purmst.c_bill_no,purmst.n_srno,purdet.c_item_code

Posted 8-Sep-19 20:03pm

Member 11337367

Updated 12-Sep-19 1:37am

Add a Solution

2 solutions

Solution 1

GROUP BY doesn't work as you think: each column you add to the GROUP BY list increases the number of rows you return, not decreases it.
Have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] - it explains how to use it.
What you need to do is two queries: One to return the max Date for each batch:

SQL

SELECT batch_no, MAX(bill_Date) FROM MyTable
GROUP BY Batch_no

Then use JOIN to combine that information with your table again to get just the rows you need.

By the way: If those dates are stored as strings, you need to change that: you should always store data in appropriate datatypes: DATE in this case. String comparisons are made on a character by character basis with the whole result being based on the first difference encountered - which works provided no-one added "01-02-2019" to you DB by mistake later on ... storing them as DATE, DATETIME, or DATETIME2 gets rid of that problem.

Posted 8-Sep-19 20:38pm

OriginalGriff

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)

Member 14588506 · Accepted Answer · 2019-09-12T01:38:00

Solution 2

Dear Programmer,
As per the requirement , its is possible by sub query as i have written.
kindly put it in your programming.

SELECT T.* FROM MM T WHERE DATE=
(SELECT MAX(DATE) FROM MM WHERE ITEMCODE=T.ITEMCODE)

Regards
Nafees

Posted 12-Sep-19 1:38am

Member 14588506