Please i need help on this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please i need help on this query

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2010-09-13 : 10:31:37

hi peers,
here is my situation :

Field1 Field2 Field3
xa group1 req_id1
xb group2 req_id2
xc group3 req_id3
xa group4 req_id4
xb group5 req_id5

i need to pull only the group3 record that comes right away after group2. Req_id's are in chronological order and groupx are texts
: i.e : req_id1 < req_id2 < req_id3 <.....

in other terms, i need the record that comes after the re cord flagged by group2..knowing that req_id3 is right after req_id2.

any thoughts ?

thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-13 : 16:09:53

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Since you also confuse rows and records, columns and field, I woudl suggest you read any book on basic RDBMS. There is no chronological order in a table unless you add a temproal column to the schema. YOu are also confusing magnetic tapes with tables. Now, let's try to fix this by making wild guesses:

CREATE TABLE Foobar
(x_code CHAR(2) NOT NULL,
grp_nbr CHAR(6) NOT NULL,
req_id CHAR(7) NOT NULL PRIMARY KEY);

WITH
X(x_code, grp_nbr, req_id, rn)
AS
(SELECT x_code, grp_nbr, req_id, ROW NUMBER() OVER (ORDER BY req_id ASC)
FROM Foobar)

SELECT X1.x_code, X1.grp_nbr, X1.req_id
FROM X AS X1, X AS X2
WHERE X2.grp_nbr = 'group2'
AND X1.req_id = X2.req_id + 1;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 12:51:13

u mean u need only those cases where group3 record is coming immediately after group2 and nothing in between?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources