?
Solved

SQL - Query returns several rows, just need first one

Posted on 2010-09-17
14
Medium Priority
?
335 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
[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
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 33700148
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
ID: 33700250
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 252 total points
ID: 33700476
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:dbaSQL
ID: 33700487
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
ID: 33701682
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
ID: 33701708
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 252 total points
ID: 33701865
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33702864
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 496 total points
ID: 33708112
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 496 total points
ID: 33708131
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
ID: 33715319
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
ID: 33715336
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
ID: 33717134
>> "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
ID: 33751756
Combination of CTE and partition resolved the issue.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

719 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