Solved

select max date from table

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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