Solved

SQL - Query returns several rows, just need first one

Posted on 2010-09-17
14
283 Views
Last Modified: 2012-05-10
I have a script which looks at a range of dates and can return more than one row because there may be more than one TRDate entry.  I just need one row.

Select 'some stuff'
From ...
where DFCS.Status = 'C'
      and DFCS.subkey is not null
            and DFCS.trdate between '2010-06-01' and '2010-06-21'
                              and DFPATH.TRtype = 'U'
order by skey ASC, trdate ASC
0
Comment
Question by:MongoWishHeWasClever
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
Well, if there are multiple records within the given date range, meeting the conditions you're passing in, you are going to get more than one row back.

can you provide some sample data?
0
 

Author Comment

by:MongoWishHeWasClever
Comment Utility
Here is an example;  you can see that the record for person10 changed on different days, and the one for Person14 changed twice on the same day.  (Field 1 = Unique key, Field 2 = Subject, Field 3 = Date (and time) of update).

Person1      IFB3      01/06/2010
Person2      ELP2      07/06/2010
Person3      ELP2      07/06/2010
Person4      IFE2      18/06/2010
Person5      IFE2      16/06/2010
Person6      IFE2      04/06/2010
Person7      IFE1      10/06/2010
Person8      ELP1      01/06/2010
Person9      IFE2      01/06/2010
Person10      ELP2      04/06/2010
Person10      ELP2      16/06/2010
Person11      IFB1      03/06/2010
Person12      L1BU      02/06/2010
Person13      ELP2      07/06/2010
Person14      ELP3      04/06/2010
Person14      ELP3      04/06/2010
Person15      IFLS      21/06/2010
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 63 total points
Comment Utility
Replace column and table names accordingly.
select skey, Subject, TRDate

FROM (

select skey, Subject, TRDate , row_number () over ( partition by skey order by skey, TRDate) rnum

From ur_tables

where DFCS.Status = 'C'

      and DFCS.subkey is not null

            and DFCS.trdate between '2010-06-01' and '2010-06-21' 

                              and DFPATH.TRtype = 'U' ) temp

WHERE rnum = 1

Open in new window

0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
ok.  with the data you've posted, if you simply group by 'some stuff', you are only going to return one value per person, where the subkey and trdate is the same, like Person14 (see my post below)    But, for Person10, your trdate is not the same, so this will come back twice.

In the situations where you've got one person in there twice, same subkey, different trdate -- which trdate would you like to return?



create table #dfcs (person varchar(15), subkey char(5) null, trdate datetime, trtype char(1),status char(1))



set dateformat dmy

insert #dfcs 

select 'Person1','IFB3','01/06/2010','U','C'

union all

select 'Person2','ELP2','07/06/2010','U','C'

union all

select 'Person3','ELP2','07/06/2010','U','C'

union all

select 'Person4','IFE2','18/06/2010','U','C'

union all

select 'Person5','IFE2','16/06/2010','U','C'

union all

select 'Person6','IFE2','04/06/2010','U','C'

union all

select 'Person7','IFE1','10/06/2010','U','C'

union all

select 'Person8','ELP1','01/06/2010','U','C'

union all

select 'Person9','IFE2','01/06/2010','U','C'

union all

select 'Person10','ELP2','04/06/2010','U','C'

union all

select 'Person10','ELP2','16/06/2010','U','C'

union all

select 'Person11','IFB1','03/06/2010','U','C'

union all

select 'Person12','L1BU','02/06/2010','U','C'

union all

select 'Person13','ELP2','07/06/2010','U','C'

union all

select 'Person14','ELP3','04/06/2010','U','C'

union all

select 'Person14','ELP3','04/06/2010','U','C'

union all

select 'Person15','IFLS','21/06/2010','U','C'







set dateformat dmy

select person,subkey,trdate,trtype,status

from #dfcs

where status = 'C' 

AND subkey is not null

and trdate between '01-06-2010' and '21-06-2010' 

group by person,subkey,trdate,trtype,status

order by person,subkey,trdate,trtype,status

Open in new window

0
 

Author Comment

by:MongoWishHeWasClever
Comment Utility
Hi rrjegan17,
I implemented your suggestion and it worked a treat until I joined to a couple more tables and needed to pull back the course code (DFPATH.Course), at which point I get this error;
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DFPATH.Course" could not be bound.
I guess this is because the field Course appears in more than one of the tables, but I do not know how to resolve this???

select Skey, Subkey, TRdate, Status, TRdet AS Old_Status, TTPeriod, Surname, BD_Names01, Study_prog, DFPATH.Course
FROM (select DFCS.skey, DFCS.Subkey, DFCS.TRDate , DFCS.Status, TRdet, TTPeriod, Surname, BD_Names01, Study_prog, DFPATH.Course,
row_number ()
over ( partition by DFCS.skey order by DFCS.skey, DFCS.TRDate) rnum
      From DFCS
      join DF on DFCS.Skey = DF.DFkey
            join DFPATH      on DFCS.Skey = DFPATH.Skey
      where DFCS.Status = 'C'
      and DFCS.subkey is not null
            and DFCS.trdate between '2010-06-01' and '2010-06-21'
                  and DFPATH.TRtype = 'U') temp
WHERE rnum = 1
0
 

Author Comment

by:MongoWishHeWasClever
Comment Utility
Hi dbaSQL,
Thanks for the explaination.
For this execise it does not matter if it is the first or last, but looking foward it would be useful to have the choice.
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 63 total points
Comment Utility
using the same #dfcs i created before,


select person,subkey,MAX(trdate) trdate
FROM #dfcs
where status = 'C'
AND subkey is not null
and trdate between '01-06-2010' and '21-06-2010'
group by person,subkey
order by person,subkey
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Fixed the issue:
select Skey, Subkey, TRdate, Status, TRdet AS Old_Status, TTPeriod, Surname, BD_Names01, Study_prog, Course

FROM (select DFCS.skey, DFCS.Subkey, DFCS.TRDate , DFCS.Status, TRdet, TTPeriod, Surname, BD_Names01, Study_prog, DFPATH.Course,

row_number () 

over ( partition by DFCS.skey order by DFCS.skey, DFCS.TRDate) rnum 

      From DFCS 

      join DF on DFCS.Skey = DF.DFkey

            join DFPATH      on DFCS.Skey = DFPATH.Skey

      where DFCS.Status = 'C' 

      and DFCS.subkey is not null 

            and DFCS.trdate between '2010-06-01' and '2010-06-21' 

                  and DFPATH.TRtype = 'U') temp 

WHERE rnum = 1

Open in new window

0
 
LVL 12

Assisted Solution

by:jagssidurala
jagssidurala earned 124 total points
Comment Utility
For these type of requirements we can use CTE(Common table expression) abd performance wise also its good.

With CTE AS
(
               select  DFCS.skey,
                           DFCS.Subkey,
DFCS.TRDate , DFCS.Status, TRdet, TTPeriod, Surname, BD_Names01, Study_prog, DFPATH.Course,
row_number ()
over ( partition by DFCS.skey order by DFCS.skey, DFCS.TRDate) rnum
      From DFCS
      join DF on DFCS.Skey = DF.DFkey
            join DFPATH      on DFCS.Skey = DFPATH.Skey
      where DFCS.Status = 'C'
      and DFCS.subkey is not null
            and DFCS.trdate between '2010-06-01' and '2010-06-21'
                  and DFPATH.TRtype = 'U'
)
0
 
LVL 12

Accepted Solution

by:
jagssidurala earned 124 total points
Comment Utility
For these type of requirements we can use CTE(Common table expression) and performance wise also its good, better to use inner join instead simple join.

With CTE AS
(
               select  DFCS.skey,
                           DFCS.Subkey,
                           DFCS.TRDate ,
                           DFCS.Status,
                           TRdet, TTPeriod,
                           Surname,
                           BD_Names01,
                           Study_prog,
                           DFPATH.Course,
                     Row_Number () Over ( partition by DFCS.skey order by DFCS.skey, DFCS.TRDate) RowId
                 From DFCS
                 Inner
                 join    DF on DFCS.Skey = DF.DFkey
                 Inner
                 join DFPATH      on DFCS.Skey = DFPATH.Skey
                where DFCS.Status = 'C'
                 and DFCS.subkey is not null
                 and DFCS.trdate between '2010-06-01' and '2010-06-21'
                 and DFPATH.TRtype = 'U'
)
Select *
From   CTE
Where RowId = 1

0
 

Author Comment

by:MongoWishHeWasClever
Comment Utility
Hi rrjegan17,
Thank for the update, but I now get this error;
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

0
 

Author Comment

by:MongoWishHeWasClever
Comment Utility
Hi jagssidurala,
The CTE solution has worked a treat.  CTEs are now my new best friend.

This is my first post on EE and I would like to thank you all for your assistance.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

If you passed in datetime values wrongly, then it would error out else it should work correctly.
0
 

Author Closing Comment

by:MongoWishHeWasClever
Comment Utility
Combination of CTE and partition resolved the issue.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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