simple sp

Question

Post reply

simple sp

nazaninahmady_sh

Default port

Points: 1465
More actions
November 20, 2010 at 7:56 am

#243071

Hi
i have a table with this format:
Filed1 Field2 Field3
the type of Filed3 is bit
i want to write a store procedure to retrieve the records : from the last record that its Filed3 is true,

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

What defines the

from the last record that its Filed3 is true,

Do you have a DATETIME or an identity field in this table?

Remember the order in which the rows are inserted, does NOT guarantee that the they will be returned in that sequence. Also a bit has a value of either 0,1 or is NULL. You define, in your system, wether a 0 or a 1 is true (A bit being true or false is a concept from ACCESS) not SQL.

For more help please define the table definition, provide some sample data and and your desired result from the sample data.

To do so please follow the first link in my signature block for assistance in supplying that information.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

Filed1 Field2 Field3

1 A 0

2 B 1

3 C 0

4 D 1

5 E 0

i want to see the records after the last Filed3=1 so i awnt to see

Filed1 Field2 Field3

5 E 0

LutzM SSC Guru Points: 107049 More actions · Answer 3

Sounds like homework to me...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

--==This is how you should have posted your tables definition

CREATE TABLE #T(Field1 INT, Field2 VARCHAR(3),Field3 BIT)

--==This is how you should have posted your sample data

INSERT INTO #T

SELECT 1, 'A', 0 UNION ALL

SELECT 2, 'B', 1 UNION ALL

SELECT 3, 'C', 0 UNION ALL

SELECT 4, 'D', 1 UNION ALL

SELECT 5, 'E', 0 UNION ALL

SELECT 6, 'F', 1 UNION ALL

SELECT 7, 'G', 0

--==Notice that I added additional rows

SELECT MAX(Field1), Field2 FROM #T WHERE Field3 = 0 GROUP BY Field2

--==Clean up after testing

DROP TABLE #T

Results:

(No column name) Field2

1 A

3 C

5 E

7 G

Now do some additional work to learn. Run this T-SQL

SELECT MAX(Field1) FROM #T WHERE Field3 = 0

and observe the result! Understand what GROUP BY clause did to alter the results.

Now run this T-SQL to learn something more about simple select statements:

SELECT MAX(Field1), Field2 FROM #T WHERE Field3 = 0

GROUP BY Field2,Field1 ORDER BY Field1 DESC

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

nazaninahmady_sh Default port Points: 1465 More actions · Answer 5

thanks but i want the records after that Max(ID), with all of their fields

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Did you not understand?

Do you have a DATETIME or an identity field in this table?
Remember the order in which the rows are inserted, does NOT guarantee that the they will be returned in that sequence. Also a bit has a value of either 0,1 or is NULL. You define, in your system, wether a 0 or a 1 is true (A bit being true or false is a concept from ACCESS) not SQL.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

nazaninahmady_sh (11/20/2010)
thanks but i want the records after that Max(ID), with all of their fields

Gosh, would you at least try? The code posted by Bit-Bucket contains the essence of what you need. Post your attempt so people can see that you're at least trying to learn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)