Solved

getting the first record of a sql table.

Posted on 2013-10-22
11
269 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
Comment Utility
The analytic function row_number() would solve this.

What is the relevance of DB2 to this question?
0
 

Author Comment

by:Butterfly2
Comment Utility
the db2 was a mistake, also can you give me an example of the row_number() function
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 295 total points
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:aheddell
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now