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
 select records slab of row in table

Author  Topic 

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-05-07 : 03:07:25
hi
i have following table structure

id empnm empage
1 A 10
2 B 20
3 C 30
4 F 40
5 G 10
6 H 20
7 I 30
8 J 40
9 K 10
10 L 20
11 M 30
12 N 40
13 O 10
14 P 20
15 Q 30

i have write a query to select record to add one colom of slab a in folloing format



id empnm empage slab
1 A 10 1-3
2 B 20 1-3
3 C 30 1-3
4 F 40 4-6
5 G 10 4-6
6 H 20 4-6
7 I 30 7-9
8 J 40 7-9
9 K 10 7-9
10 L 20 10-12
11 M 30 10-12
12 N 40 10-12
13 O 10 13-15
14 P 20 13-15
15 Q 30 13-15

so how it is posible select salb of records
thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 03:14:08
I can't understand what you are doing and what is your wanted output.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-05-07 : 03:19:18
i can find the slab of rows in tabel
like first three records in 1-3 salb
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-05-07 : 11:20:43
Sorry for the language barrier (if that's what it is??) but to me a "slab" is usually a huge chunk of meat or a nice size crappie on the end of my fishing line!

Anyway, what determines the value of slab? Is it always IDs of three rows of data, staring from the last record updated + 1? There really doesn't seem to be any relationship among the records. More info from you would help.

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 13:45:43
are you using sql 2005?

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

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-07 : 15:39:07
Probalby some better ways, but I felt like messing around with CTEs:
-- Setup Sample Data
DECLARE @Table TABLE (id INT, empnm CHAR(1), empage INT, slab VARCHAR(8))
INSERT @Table (id, empnm, empage)
SELECT 1, 'A', 10
UNION ALL SELECT 2, 'B', 20
UNION ALL SELECT 3, 'C', 30
UNION ALL SELECT 4, 'F', 40
UNION ALL SELECT 5, 'G', 10
UNION ALL SELECT 6, 'H', 20
UNION ALL SELECT 7, 'I', 30
UNION ALL SELECT 8, 'J', 40
UNION ALL SELECT 9, 'K', 10
UNION ALL SELECT 10, 'L', 20
UNION ALL SELECT 11, 'M', 30
UNION ALL SELECT 12, 'N', 40
UNION ALL SELECT 13, 'O', 10
UNION ALL SELECT 14, 'P', 20
UNION ALL SELECT 15, 'Q', 30

-- Variable for Slab Size..
DECLARE @SlabSize INT
SET @SlabSize = 3

-- Query to Setup CTE and Update table
;WITH Cte
AS
(
SELECT
ID,
ROW_NUMBER() OVER (ORDER BY id) -
CASE
WHEN ROW_NUMBER() OVER (ORDER BY id) % @SlabSize = 0 THEN @SlabSize
ELSE ROW_NUMBER() OVER (ORDER BY id) % @SlabSize
END AS GroupNum
FROM
@Table
)
UPDATE
T
SET
Slab = D.Slab
FROM
@Table AS T
INNER JOIN
(
SELECT
ID,
CAST(MIN(ID) OVER (PARTITION BY GroupNum) AS VARCHAR(2))
+ ' - '
+ CAST(MAX(ID) OVER (PARTITION BY GroupNum) AS VARCHAR(2)) AS Slab
FROM Cte
) AS D
ON T.ID = D.ID

-- Verify Results
SELECT *
FROM @Table
EDIT: Small format change.
Go to Top of Page
   

- Advertisement -