Click here to Skip to main content
14,298,693 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 4 days ago
Rate this:
Please Sign up or sign in to vote.

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:
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.
   
Rate this:
Please Sign up or sign in to vote.

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100