select max date from table

Posted on 2008-10-02
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 93

Expert Comment

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

Accepted Solution

BrandonGalderisi earned 2000 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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