Solved

getting the first record of a sql table.

Posted on 2013-10-22
11
271 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
11 Comments
 
LVL 48

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 48

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 295 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 48

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 48

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 69

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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