Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL - Query returns several rows, just need first one

Posted on 2010-09-17
14
Medium Priority
?
338 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 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

926 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