Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

select max date from table

Posted on 2008-10-02
3
Medium Priority
?
801 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 93

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 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

577 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