[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select specific records and its 2 preceedins ones

Posted on 2011-04-29
15
Medium Priority
?
307 Views
Last Modified: 2012-05-11
I have a table many records one fields per record. (name and id)

Many records have a '.'. I can easily select those

select * from table where name like '%.%' order by id

But I need a query that will select the record with the field with a '.' and the 2 previous ones (as ordered by id)





0
Comment
Question by:robrodp
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35492817

Do you mean two characters before the '.'

select * from table where name like '??.%' order by id
0
 

Author Comment

by:robrodp
ID: 35492826
No´, I mean the records preceeding the one with the ´.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35492964
Do you know the id, ie. do you wish to query to get the prior records given the id of a record with a '.', already in hand, or do you want a query that gives ALL records in the table with the '.', along with each record's preceding 2 records?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:robrodp
ID: 35492981
I know the id, ie. I wish to query to get the prior records given the id of a record with a '.', already in hand
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35493008
select top 3 * from t where id <= @Id
order by id desc
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 35493026
select *
from mytable t1
join mytable t2 ON t2.ID = t1.ID -1
join mytable t3 ON t3.ID = t1.ID -2
where t1.name like '%.%'
and t1.ID >= 3
0
 

Author Comment

by:robrodp
ID: 35493172
danrosenthal:

Works like a charm Now I ned to update these 2 prior records. Put a dot al the end of the name
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35493183
Keep in mind, dan's solution assumes the ID values are contiguous (no gaps). This is reasonable assumption if you never delete records.
0
 
LVL 15

Accepted Solution

by:
danrosenthal earned 1600 total points
ID: 35493211
I would create a list of IDs to update and then run on update against all of those IDs.
You can do it all at once with something like this:

UPDATE m
SET name = name + '.'
FROM mytable m
  WHERE ID IN (
  select t2.ID
  from mytable t1
  join mytable t2 ON t2.ID = t1.ID -1
  where t1.name like '%.%'
  and t1.ID >= 3
  UNION
  select t3.ID
  from mytable t1
  join mytable t3 ON t3.ID = t1.ID -2
  where t1.name like '%.%'
  and t1.ID >= 3
)
0
 
LVL 6

Assisted Solution

by:hyphenpipe
hyphenpipe earned 400 total points
ID: 35493355
with recordsCTE
as
(select name, row = row_number() over (order by id) from table_name)
  , set1 as
   (select * from recordsCTE
    where name like '%.%')
  , set2 as
      (select r1.name from recordsCTE r1, set1
       where r1.row = set1.row - 1)
  , set3 as
      (select r2.name from recordsCTE r2, set1
       where r2.row = set1.row - 2)
  , results as
       (select name from set1
        union all
        select name from set2
        union all
        select name from set3)
update results set name = name + '.'
where first_name not like '%.%'
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35493370
er replace that last line to read:

where name not like '%.%'
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35493464
My bad, CTES with unions are not update-able.
0
 

Author Comment

by:robrodp
ID: 35493486
No problem I just ran it twice
0
 

Author Closing Comment

by:robrodp
ID: 35493742
Boy am I green on this sql stuff, Thx a lot
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35494058
No problem.
0

Featured Post

Independent Software Vendors: 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

In this article I will describe the Copy Database Wizard 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.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 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