Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

getting the first record of a sql table.

Posted on 2013-10-22
11
Medium Priority
?
280 Views
Last Modified: 2013-10-28
Good Morning,

I have a query, that is duplicating because of one field that I added. So basically I just want to get the first of this paticular field.

Here is my query:

select ch.episode_no as PtNo,
       c.rpt_name as PtName,
       ch.cng_type as ChngTyp,
       c.start_dtime as AdmitDate,
       ch.xfer_eff_dtime as DschDate,
       ch.pract_no_from as AtnDrCd,
        c.vip_ind as VIP,
       o.ord_no AS OrdNum,
       o.ord_sts as OrdSts,
       o.svc_cd as SvcCode,
       o.svc_dept as SvcDept,
       o.desc_as_written as OrderDesc,
      u.data_text as OrderComments
From  smsmir.mir_cen_hist as ch
      inner join SMSPHDSSGCB0.smsmir.mir_pms_case as c
                   on  c.episode_no=ch.episode_no
             and c.pt_id = ch.pt_id
             and c.src_sys_id = ch.src_sys_id
             and c.pt_id_start_dtime = ch.pt_id_start_dtime
      inner join smsmir.mir_ord as o
             on  o.src_sys_id = ch.src_sys_id
             and o.orgz_cd = ch.orgz_cd
             and o.pt_id = ch.pt_id
             and o.episode_no = ch.episode_no
             and o.vst_id = ch.vst_id
      LEFT join smsmir.mir_ord_user u
                on  ch.src_sys_id = u.src_sys_id
                and ch.orgz_cd = u.orgz_cd
                and ch.pt_id = u.pt_id
                and ch.episode_no = u.episode_no
                and ch.pt_id_start_dtime = u.pt_id_start_dtime
                and ch.vst_id = u.vst_id
                and ch.vst_no = u.vst_no
                and u.data_cd = '3ORDCM01'
            
where Convert(varchar(10), ch.xfer_eff_dtime, 112)>= Convert(varchar(10),getdate()-30,112)
        and ch.episode_no in(select top 1 episode_no
                         from smsmir.mir_ord
                         where episode_no = ch.episode_no
                               and src_sys_id = ch.src_sys_id
                       and orgz_cd = ch.orgz_cd
                       and svc_dept = 'LAB'
                       AND ord_sts = 'CANCELED')
       and u.data_text = (select MIN(u2.data_text)
                                   from smsmir.mir_ord_user u2
                                   where u.ord_no = u2.ord_no
                                   and u.episode_no = u2.episode_no
                                   and u.src_sys_id = u2.src_sys_id
                                   and u.pt_id = u2.pt_id
                                   and u.orgz_cd = u2.orgz_cd
                                   and u.pt_id_start_dtime = u2.pt_id_start_dtime
                                   and u.data_cd = u2.data_cd
                                   and u.intrn_seq_no = u2.intrn_seq_no)
   
    order by  PtName,
              ord_sts,
              svc_dept


the user_data text field is the one that is creating duplicat records, so I basically just want the first order comment for each row.
0
Comment
Question by:Butterfly2
[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
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39590746
The analytic function row_number() would solve this.

What is the relevance of DB2 to this question?
0
 

Author Comment

by:Butterfly2
ID: 39590755
the db2 was a mistake, also can you give me an example of the row_number() function
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39590764
Seems like pure TSQL so I will ignore the DB2 topic.

Instead of joining directly to smsmir.mir_ord_user I suggest joining to a subquery that uses row_number() - BUT I need to know what information in that table would identify " the first" record i.e. on what basis do we choose "the first"?

Is there a date field perhaps (e.g. date_created) or can we use the ID field of that table?

-----------
BTW:
instead of this
WHERE CONVERT(varchar(10), ch.xfer_eff_dtime, 112) >= CONVERT(varchar(10), GETDATE() - 30, 112)

try this:
WHERE ch.xfer_eff_dtime >= convert(datetime,CONVERT(varchar(10), GETDATE() - 30, 112))


It is better for performance to avoid using functions on the data, just flip the criteria to the right data type instead.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1180 total points
ID: 39590778
Here is a guess, replace the left join to smsmir.mir_ord_user with this:
 LEFT join (
            select                
                      src_sys_id
                    , orgz_cd
                    , pt_id
                    , episode_no
                    , pt_id_start_dtime
                    , vst_id
                    , vst_no
                    , data_cd
                    , row_number() over (PARTITION BY src_sys_id, orgz_cd, pt_id, episode_no, vst_id, vst_no 
                                         ORDER BY pt_id_start_dtime ASC)
                        as rn
            from smsmir.mir_ord_user
            ) u
                on  ch.src_sys_id = u.src_sys_id
                and ch.orgz_cd = u.orgz_cd
                and ch.pt_id = u.pt_id
                and ch.episode_no = u.episode_no
                and ch.pt_id_start_dtime = u.pt_id_start_dtime
                and ch.vst_id = u.vst_id
                and ch.vst_no = u.vst_no
                and u.data_cd = '3ORDCM01'
                and u.rn = 1 -- <<<<<<<<<<<< this should produce "the first"

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39590793
in case it isn't obvious, the subquery can be run on it's own to judge the results, that way the "partition" and the "order" being used to determine row_number can be tuned to suit.
            select                
                      src_sys_id
                    , orgz_cd
                    , pt_id
                    , episode_no
                    , pt_id_start_dtime
                    , vst_id
                    , vst_no
                    , data_cd
                    , row_number() over (PARTITION BY src_sys_id, orgz_cd, pt_id, episode_no, vst_id, vst_no 
                                         ORDER BY pt_id_start_dtime ASC)
                        as rn
            from smsmir.mir_ord_user
where data_cd = '3ORDCM01'

Open in new window

for row_number() documentation see: http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx
0
 
LVL 2

Expert Comment

by:aheddell
ID: 39591004
Surely an in line subquery would do this best:


select ch.episode_no as PtNo,
       c.rpt_name as PtName,
       ch.cng_type as ChngTyp,
       c.start_dtime as AdmitDate,
       ch.xfer_eff_dtime as DschDate,
       ch.pract_no_from as AtnDrCd,
        c.vip_ind as VIP,
       o.ord_no AS OrdNum,
       o.ord_sts as OrdSts,
       o.svc_cd as SvcCode,
       o.svc_dept as SvcDept,
       o.desc_as_written as OrderDesc,
     (select top 1 u.data_text from smsmir.mir_ord_user u
                where  ch.src_sys_id = u.src_sys_id
                and ch.orgz_cd = u.orgz_cd
                and ch.pt_id = u.pt_id
                and ch.episode_no = u.episode_no
                and ch.pt_id_start_dtime = u.pt_id_start_dtime
                and ch.vst_id = u.vst_id
                and ch.vst_no = u.vst_no
                and u.data_cd = '3ORDCM01'
          order by u.data_text)
       as OrderComments
From  smsmir.mir_cen_hist as ch
      inner join SMSPHDSSGCB0.smsmir.mir_pms_case as c 
                   on  c.episode_no=ch.episode_no
             and c.pt_id = ch.pt_id
             and c.src_sys_id = ch.src_sys_id
             and c.pt_id_start_dtime = ch.pt_id_start_dtime
      inner join smsmir.mir_ord as o
             on  o.src_sys_id = ch.src_sys_id
             and o.orgz_cd = ch.orgz_cd
             and o.pt_id = ch.pt_id
             and o.episode_no = ch.episode_no
             and o.vst_id = ch.vst_id
 
            
where Convert(varchar(10), ch.xfer_eff_dtime, 112)>= Convert(varchar(10),getdate()-30,112)
        and ch.episode_no in(select top 1 episode_no
                         from smsmir.mir_ord
                         where episode_no = ch.episode_no 
                               and src_sys_id = ch.src_sys_id
                       and orgz_cd = ch.orgz_cd
                       and svc_dept = 'LAB'
                       AND ord_sts = 'CANCELED')

   
    order by  PtName,
              ord_sts,
              svc_dept

Open in new window


Apologies if I have missed something here...

Cheers

Alex
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39591062
correlated subqueries in the select clause are not my first choice approach
(as a single analytic function is an efficient method for determining this style of need)

but without the ability to test it - perhaps its appropriate

the problem of how to sort the result still exists
 (i.e. is sorting by order by u.data_text right? I don't know)
0
 
LVL 2

Expert Comment

by:aheddell
ID: 39591113
They are not necessarily the most efficient I would agree.  However, they aren't that bad either - depends on the size of your dataset.  They are however very "readable" so if this query needs to be supported in the future, they have some mileage.

As for the order, yes you are correct that I have sorted on u.data_text as that's what appeared to be in the original query.  Maybe I misread that
0
 
LVL 2

Expert Comment

by:aheddell
ID: 39591137
Just thought.  Given that there are no joins in the subquery, if you do not ORDER it, SQL Server will return in the table order thus the TOP 1 will return the first record.  This may assume you have no Clustered indexes - not sure on that one

Cheers

Alex
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39594713
Get rid of the JOIN to the "u" table.  Just use a subquery within the SELECT, something like below.  You change the ORDER BY the proper columns to get the specific data_text you want.


select ch.episode_no as PtNo,
       c.rpt_name as PtName,
       ch.cng_type as ChngTyp,
       c.start_dtime as AdmitDate,
       ch.xfer_eff_dtime as DschDate,
       ch.pract_no_from as AtnDrCd,
        c.vip_ind as VIP,
       o.ord_no AS OrdNum,
       o.ord_sts as OrdSts,
       o.svc_cd as SvcCode,
       o.svc_dept as SvcDept,
       o.desc_as_written as OrderDesc,
       (SELECT TOP (1) data_text FROM smsmir.mir_ord_user u
        WHERE ch.src_sys_id = u.src_sys_id
                and ch.orgz_cd = u.orgz_cd
                and ch.pt_id = u.pt_id
                and ch.episode_no = u.episode_no
                and ch.pt_id_start_dtime = u.pt_id_start_dtime
                and ch.vst_id = u.vst_id
                and ch.vst_no = u.vst_no
                and u.data_cd = '3ORDCM01'
        ORDER BY u.whatever_column --<<-- change as needed
       ) as OrderComments
From  smsmir.mir_cen_hist as ch
      inner join SMSPHDSSGCB0.smsmir.mir_pms_case as c
                   on  c.episode_no=ch.episode_no
             and c.pt_id = ch.pt_id
             and c.src_sys_id = ch.src_sys_id
             and c.pt_id_start_dtime = ch.pt_id_start_dtime
      inner join smsmir.mir_ord as o
             on  o.src_sys_id = ch.src_sys_id
             and o.orgz_cd = ch.orgz_cd
             and o.pt_id = ch.pt_id
             and o.episode_no = ch.episode_no
             and o.vst_id = ch.vst_id
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 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