Solved

SQL - Query returns several rows, just need first one

Posted on 2010-09-17
14
305 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
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 63 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 63 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 124 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 124 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

773 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