SQL - Query returns several rows, just need first one

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
MongoWishHeWasCleverAsked:
Who is Participating?
 
jagssiduralaConnect With a Mentor Commented:
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
 
dbaSQLCommented:
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
 
MongoWishHeWasCleverAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
dbaSQLCommented:
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
 
MongoWishHeWasCleverAuthor Commented:
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
 
MongoWishHeWasCleverAuthor Commented:
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
 
dbaSQLConnect With a Mentor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
jagssiduralaConnect With a Mentor Commented:
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
 
MongoWishHeWasCleverAuthor Commented:
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
 
MongoWishHeWasCleverAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> "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
 
MongoWishHeWasCleverAuthor Commented:
Combination of CTE and partition resolved the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.