Modifying the row located 2 positions before last

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

SQL Server 2000 Forums

Transact-SQL (2000)

Modifying the row located 2 positions before last

Author

Topic

paradise_wolf
Starting Member

32 Posts

Posted - 2006-09-15 : 18:09:41

I want to create a procedure where each time I add a new record to a table I want to set the field “Display” ( BIT ) to “false” in a position that is two rows before the last one.

How to do that ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 18:11:18

Do you have a column that defines the row position?

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 01:50:05

Is this so that you can select the "last two rows" at some other part of your application? 'Coz I reckon there are easier ways of doing that rather than un-flagging all-bar the last two rows in the table!

Kristen

paradise_wolf
Starting Member

32 Posts

Posted - 2006-09-16 : 08:35:39

Hi Tkizer an Kristen

<<Do you have a column that defines the row position?>>

Yes, it has the “CartoonID” column (field ) that is set with identity starting from 1 and increasing by 1.

There is a problem however, after testing addition of more records ( rows ) and then deleting them through Transact-SQL in order to make new tests, I notice that the numerical order of CartoonID is broken. For instance, I had a sequence of values: 1,2 and 3. Then I deleted the row where CartoonID = 3 through Transact-SQL and then when I tested the C# code routine that adds a new record, it broke the sequential order. Now it shows 1,2,4. So I am not sure if I can rely in the CartoonID values to determine the numerical order of the rows ( records ).

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-16 : 08:41:46

order is either ascending or descending.
Identity is a column that just unique-ifys a row.
so you can't rely on it to keep a gap free order, because it isn't meant for that.
numbering should be done on the presentation layer anyway.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

paradise_wolf
Starting Member

32 Posts

Posted - 2006-09-16 : 09:06:25

Hi spirit1

What I aim to do is this: If for example, I had 5 records with CartoonID field having sequentially values 1,2,3,4,5 with 1,2,3 set to "false" and 4,5 set to "true" in the "Display" field ( column ) then when I add a new record ( with CartoonID = 6 ), I wanted to set the "Display" value to "false" in the record with CartoonID = 4. Because I want the web page to show always the last two cartoons and hide the rest.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-16 : 10:34:13

Why not just use:

SELECT TOP 2 CartoonID FROM Cartoons ORDER BY CartoonID DESC

Now you don't have to update anything and the actual numbers don't matter. If you don't plan on displaying more than 2 rows of data, why select more?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-16 : 17:54:31

Using Rob's suggestion,

UPDATE		c
SET		c.Display = CASE WHEN t.CartoonID IS NULL THEN "False" ELSE "True" END
FROM		Cartoons c
LEFT JOIN	(
			SELECT TOP 2	CartoonID
			FROM		Cartoons
			ORDER BY	CartoonID DESC
		) t ON t.CartoonID = c.CartoonID

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-17 : 02:24:42

"Because I want the web page to show always the last two cartoons and hide the rest"

Don't waste a column on a display flag then, use the query Rob suggests to get the most recent 2 rows.

If you need those two rows in ASCENDING order then do:

SELECT *
FROM Cartoons
WHERE CartoonID IN (SELECT TOP 2 CartoonID FROM Cartoons ORDER BY CartoonID DESC)
ORDER BY CartoonID ASC

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources