?
Solved

Select the most recently created row

Posted on 2012-09-05
9
Medium Priority
?
458 Views
Last Modified: 2012-09-05
Hi,

How would I be able to modify this query to select only the most recent [MaxxDate] from db?

The results look like this:
NAME--------------------------MAXXDATE----------------------------
Test, Person----------------7/1/2012-------------------------------
Test, Person----------------4/20/2012-----------------------------
Smith, Allen--------------------5/23/2012----------------------------
Smith, Allen--------------------3/10/2012----------------------------

I would like the results to only show:
NAME--------------------------MAXXDATE----------------------------
Test, Person----------------7/1/2012-------------------------------
Smith, Allen--------------------5/23/2012----------------------------

Query:
SELECT 
((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
, ((convert(varchar, convert(datetime, (SELECT max(v) 
   FROM (VALUES (paq.create_timestamp)
				, (txp.init_tx_plan_signed)
				, (txp.tx_plan_rev_signed_1)
				, (txp.tx_plan_rev_signed_2)
				, (txp.tx_plan_rev_signed_3)
				, (txp.tx_plan_rev_signed_4)
				, (txp.tx_plan_rev_signed_5)
				, (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
 FROM   
 sftu_JOE_TX_PLAN_HX txp 
 FULL OUTER JOIN person p 
 ON txp.person_id=p.person_id
 LEFT OUTER JOIN patient_status ps 
 ON p.person_id=ps.person_id
 LEFT OUTER JOIN patient_documents pd 
 ON p.person_id=pd.person_id
 LEFT OUTER JOIN patient_provider pp 
 ON p.person_id=pp.person_id
 LEFT OUTER JOIN paq_signoff_history paq 
 ON pd.person_id=paq.person_id AND 
 pd.enc_id=paq.enc_id AND 
 pd.document_id=paq.item_id
 LEFT OUTER JOIN signatures s 
 ON pd.document_id=s.doc_id
 LEFT OUTER JOIN patient_encounter pe 
 ON pd.enc_id=pe.enc_id AND 
 pd.person_id=pe.person_id
 LEFT OUTER JOIN patient_status_mstr psm 
 ON ps.patient_status_id=psm.patient_status_id
 WHERE  
 paq.signoff_action='A' AND 
 pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
 psm.description='Active' AND 
 paq.create_timestamp>={ts '2012-03-20 00:31:08'} AND 
 (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
 
order by 1, 2 desc

Open in new window

0
Comment
Question by:susnewyork
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38368316
Have you tried grouping and selecting MAX()?
Something like:

SELECT 
((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
, MAX(((convert(varchar, convert(datetime, (SELECT max(v) 
   FROM (VALUES (paq.create_timestamp)
				, (txp.init_tx_plan_signed)
				, (txp.tx_plan_rev_signed_1)
				, (txp.tx_plan_rev_signed_2)
				, (txp.tx_plan_rev_signed_3)
				, (txp.tx_plan_rev_signed_4)
				, (txp.tx_plan_rev_signed_5)
				, (txp.tx_plan_rev_signed_6)) AS value(v))), 101)))) as [maxxDate]
 FROM   
 sftu_JOE_TX_PLAN_HX txp 
 FULL OUTER JOIN person p 
 ON txp.person_id=p.person_id
 LEFT OUTER JOIN patient_status ps 
 ON p.person_id=ps.person_id
 LEFT OUTER JOIN patient_documents pd 
 ON p.person_id=pd.person_id
 LEFT OUTER JOIN patient_provider pp 
 ON p.person_id=pp.person_id
 LEFT OUTER JOIN paq_signoff_history paq 
 ON pd.person_id=paq.person_id AND 
 pd.enc_id=paq.enc_id AND 
 pd.document_id=paq.item_id
 LEFT OUTER JOIN signatures s 
 ON pd.document_id=s.doc_id
 LEFT OUTER JOIN patient_encounter pe 
 ON pd.enc_id=pe.enc_id AND 
 pd.person_id=pe.person_id
 LEFT OUTER JOIN patient_status_mstr psm 
 ON ps.patient_status_id=psm.patient_status_id
 WHERE  
 paq.signoff_action='A' AND 
 pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
 psm.description='Active' AND 
 paq.create_timestamp>={ts '2012-03-20 00:31:08'} AND 
 (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
GROUP BY ((p.last_name+', '+p.first_name+' '+p.middle_name))
 order by 1, 2 desc

Open in new window

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38368341
Hi Sus,

This query is complicated because you have to build the date from the components, but treating the "main" query as an aggregated subquery should do it.


Hope I didn't miss anything.

Kent


SELECT Name, MaxDate
FROM
(
  SELECT id, name, max (maxxDate)
  FROM
  (
		SELECT p.id, ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
		, ((convert(varchar, convert(datetime, (SELECT max(v) 
			 FROM (VALUES (paq.create_timestamp)
						, (txp.init_tx_plan_signed)
						, (txp.tx_plan_rev_signed_1)
						, (txp.tx_plan_rev_signed_2)
						, (txp.tx_plan_rev_signed_3)
						, (txp.tx_plan_rev_signed_4)
						, (txp.tx_plan_rev_signed_5)
						, (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
	 sftu_JOE_TX_PLAN_HX txp 
	 FULL OUTER JOIN person p 
	 ON txp.person_id=p.person_id
	)
	GROUP BY id, name
) p.
LEFT OUTER JOIN patient_status ps 
  ON p.person_id=ps.person_id
LEFT OUTER JOIN patient_documents pd 
  ON p.person_id=pd.person_id
LEFT OUTER JOIN patient_provider pp 
  ON p.person_id=pp.person_id
LEFT OUTER JOIN paq_signoff_history paq 
  ON pd.person_id=paq.person_id 
  AND pd.enc_id=paq.enc_id 
  AND pd.document_id=paq.item_id
LEFT OUTER JOIN signatures s 
  ON pd.document_id=s.doc_id
LEFT OUTER JOIN patient_encounter pe 
  ON pd.enc_id=pe.enc_id 
  AND pd.person_id=pe.person_id
LEFT OUTER JOIN patient_status_mstr psm 
  ON ps.patient_status_id=psm.patient_status_id
WHERE paq.signoff_action='A' 
  AND pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' 
  AND psm.description='Active' 
  AND paq.create_timestamp>={ts '2012-03-20 00:31:08'} 
  AND (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')

order by 1, 2 desc

Open in new window

0
 
LVL 1

Author Comment

by:susnewyork
ID: 38368344
Yea, but it won't run the query.

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 13

Expert Comment

by:LIONKING
ID: 38368381
Well then you can do as instructed by Kdo, or use a CTE...
Either should do the trick.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38368401
Sorry, I missed the inner aggregation.  Hold on....
0
 
LVL 1

Author Comment

by:susnewyork
ID: 38368441
Ok thanks.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38368452
This is closer, though there may still be an issue with the inner query.

Kent
SELECT Name, MaxDate
FROM
(
  SELECT id, name, max (xDate) maxxDate
  FROM
  (
    SELECT p.id, ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
    , ((convert(varchar, convert(datetime, (SELECT v 
       FROM (VALUES (paq.create_timestamp)
            , (txp.init_tx_plan_signed)
            , (txp.tx_plan_rev_signed_1)
            , (txp.tx_plan_rev_signed_2)
            , (txp.tx_plan_rev_signed_3)
            , (txp.tx_plan_rev_signed_4)
            , (txp.tx_plan_rev_signed_5)
            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [xDate]
    FROM sftu_JOE_TX_PLAN_HX txp 
    FULL OUTER JOIN person p 
      ON txp.person_id=p.person_id
  )
  GROUP BY id, name
) p.
LEFT OUTER JOIN patient_status ps 
  ON p.person_id=ps.person_id
LEFT OUTER JOIN patient_documents pd 
  ON p.person_id=pd.person_id
LEFT OUTER JOIN patient_provider pp 
  ON p.person_id=pp.person_id
LEFT OUTER JOIN paq_signoff_history paq 
  ON pd.person_id=paq.person_id 
  AND pd.enc_id=paq.enc_id 
  AND pd.document_id=paq.item_id
LEFT OUTER JOIN signatures s 
  ON pd.document_id=s.doc_id
LEFT OUTER JOIN patient_encounter pe 
  ON pd.enc_id=pe.enc_id 
  AND pd.person_id=pe.person_id
LEFT OUTER JOIN patient_status_mstr psm 
  ON ps.patient_status_id=psm.patient_status_id
WHERE paq.signoff_action='A' 
  AND pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' 
  AND psm.description='Active' 
  AND paq.create_timestamp>={ts '2012-03-20 00:31:08'} 
  AND (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')

order by 1, 2 desc

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38368825
SELECT
    Name, MAX(maxxDate) AS MaxxDate
FROM (
    SELECT
    ((p.last_name+', '+p.first_name+' '+p.middle_name)) Name
    , ((convert(varchar, convert(datetime, (SELECT max(v)
       FROM (VALUES (paq.create_timestamp)
                            , (txp.init_tx_plan_signed)
                            , (txp.tx_plan_rev_signed_1)
                            , (txp.tx_plan_rev_signed_2)
                            , (txp.tx_plan_rev_signed_3)
                            , (txp.tx_plan_rev_signed_4)
                            , (txp.tx_plan_rev_signed_5)
                            , (txp.tx_plan_rev_signed_6)) AS value(v))), 101))) as [maxxDate]
     FROM  
     sftu_JOE_TX_PLAN_HX txp
     FULL OUTER JOIN person p
     ON txp.person_id=p.person_id
     LEFT OUTER JOIN patient_status ps
     ON p.person_id=ps.person_id
     LEFT OUTER JOIN patient_documents pd
     ON p.person_id=pd.person_id
     LEFT OUTER JOIN patient_provider pp
     ON p.person_id=pp.person_id
     LEFT OUTER JOIN paq_signoff_history paq
     ON pd.person_id=paq.person_id AND
     pd.enc_id=paq.enc_id AND
     pd.document_id=paq.item_id
     LEFT OUTER JOIN signatures s
     ON pd.document_id=s.doc_id
     LEFT OUTER JOIN patient_encounter pe
     ON pd.enc_id=pe.enc_id AND
     pd.person_id=pe.person_id
     LEFT OUTER JOIN patient_status_mstr psm
     ON ps.patient_status_id=psm.patient_status_id
     WHERE  
     paq.signoff_action='A' AND
     pp.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND
     psm.description='Active' AND
     paq.create_timestamp>={ts '2012-03-20 00:31:08'} AND
     (pd.template_file='bh_master_treatment' OR pd.template_file='sftubh_treatment_plan_review')
) AS derived
group by name
order by name
0
 
LVL 1

Author Closing Comment

by:susnewyork
ID: 38368976
Thanks everyone.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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