?
Solved

selecting with same time date stamp

Posted on 2014-12-09
5
Medium Priority
?
101 Views
Last Modified: 2014-12-10
I have a sql script shown below. The script will set all records (Except the very first one in the query) to a value of 'I###'
if the value contains the characters 'IAEE'. That is  exactly what I want the script to do.
However the script will fail if the very first record (Earliest time date stamp) and another record with 'IAEE' has the same time date
stamp. How can I fix the query so that it will work if two records have the same time date stamp (Namely if two records have the
same date and the earliest time date stamp).
I have scripted that database table and it is included as an attachement.

The query works fine unless their are two records and they both have the very same date. Is their a way to correct the query so that
t
select * from Goop
 
 update t
   set code = 'I###'
   from Goop t
   where t.DateEntered >= ( select min(x.DateEntered) from Goop x  WHERE SUBSTRING(Code,1,1) = 'I') AND SUBSTRING(Code,1,1) = 'I'
tz.txt
0
Comment
Question by:brgdotnet
  • 3
5 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1800 total points
ID: 40490578
Derived from an approach already provided by angeliii in a former question
;WITH DATA
AS (
      SELECT
            ssn
          , code
          , ROW_NUMBER() OVER (PARTITION BY code ORDER BY ssn ASC) RN
      FROM yourtable
      WHERE code = '####'
   )
UPDATE DATA
SET code = 'I###'
WHERE rn = 1

Open in new window

The advantage of row_number() is that it can ONLY supply the value 1 once within each PARTITION, so even if there are multiple records sharing a SSN only one of these will be chosen.
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
ID: 40491244
Why are you working with SUBSTRING? Like this won't work?
update t
    set code = 'I###'
    from Goop t
    where t.DateEntered >= ( select min(x.DateEntered) from Goop WHERE Code = 'I###')
       AND Code = 'IAEE' 

Open in new window

0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40491246
The solution you proposed does not work?
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40491248
Thanks Victor I believe yours works
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40493053
The first solution works. And the second one by Vitor does not. Thanks anyway guys. The solution wasn't quite was I was looking for, I think I might have to rephrase the question.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

600 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