select max date from table

Posted on 2008-10-02
Last Modified: 2010-04-21
I have a table which has a decription field and when the last update was made.  I need to pull all the records for each of the descriptions which have the last update date.  My problem is that for each record with the same description there may be records with the latest date and expample would be for just one drug description  I need all the records that have the date 20080722 for another drug description the last update may have been 20080621.  so I need the records of the max date the drug was last updated.  the code below does not work because the ndc is different for all of them and gives me all the records.  thanks for your help

      10544030328      CARISOPRODOL 350 MG TABLET          20080722
      54569170900      CARISOPRODOL 350 MG TABLET          20080722
      54569170901      CARISOPRODOL 350 MG TABLET          20080722
      54569170902      CARISOPRODOL 350 MG TABLET          20080722
      54569170903      CARISOPRODOL 350 MG TABLET          20080722
      54569170904      CARISOPRODOL 350 MG TABLET          20080722
      54569170907      CARISOPRODOL 350 MG TABLET          20080722
      54569170908      CARISOPRODOL 350 MG TABLET          20080722
      54569170909      CARISOPRODOL 350 MG TABLET          20080722
      54569340300      CARISOPRODOL 350 MG TABLET          20080722
      54569340301      CARISOPRODOL 350 MG TABLET          20080722
      54569340304      CARISOPRODOL 350 MG TABLET          20080722
      54569340305      CARISOPRODOL 350 MG TABLET          20080722
      54569340309      CARISOPRODOL 350 MG TABLET          20080722
      42549030330      CARISOPRODOL 350 MG TABLET          20080619
      42549030390      CARISOPRODOL 350 MG TABLET          20080619
      00440723820      CARISOPRODOL 350 MG TABLET          20080527
      10544030330      CARISOPRODOL 350 MG TABLET          20080410
      10544030340      CARISOPRODOL 350 MG TABLET          20080410
      10544030360      CARISOPRODOL 350 MG TABLET          20080410
      10544030390      CARISOPRODOL 350 MG TABLET          20080410
      62756044605      CARISOPRODOL 350 MG TABLET          20080219
SELECT     ndc, ln, MAX(bbdc) AS Expr1

FROM         RNDC14

GROUP BY ndc, ln

Open in new window

Question by:running32
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22626624
SELECT     ln, MAX(bbdc) AS Expr1
FROM         RNDC14
LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22626634
Remove the NDC from the group by, and add it later...

This will show you ndc, ln and bbdc for the max modified date

select r.* from rndc14 r
(SELECT     ln, MAX(bbdc) maxbbdc
FROM         RNDC14
GROUP BY ln) a
on r.ln=a.ln
and r.bbdc = a.maxbbdc

Author Closing Comment

ID: 31502479
Thank you

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
SQL Server 2008 R2 - Execution Plan 3 57
SQL Query - Oracle 10g - Subract date from next data row 4 53
SQL Query Syntax Join 4 34
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now