Solved

select max date from table

Posted on 2008-10-02
3
789 Views
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

0
Comment
Question by:running32
[X]
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
3 Comments
 
LVL 92

Expert Comment

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

Accepted Solution

by:
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
join
(SELECT     ln, MAX(bbdc) maxbbdc
FROM         RNDC14
GROUP BY ln) a
on r.ln=a.ln
and r.bbdc = a.maxbbdc
0
 

Author Closing Comment

by:running32
ID: 31502479
Thank you
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

738 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