• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

getting the first record of a sql table.

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
Butterfly2
Asked:
Butterfly2
1 Solution
 
PortletPaulCommented:
The analytic function row_number() would solve this.

What is the relevance of DB2 to this question?
0
 
Butterfly2Author Commented:
the db2 was a mistake, also can you give me an example of the row_number() function
0
 
PortletPaulCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
aheddellCommented:
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
 
PortletPaulCommented:
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
 
aheddellCommented:
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
 
aheddellCommented:
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
 
Scott PletcherSenior DBACommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now