top - 1 ROW !

Question

Post reply

top - 1 ROW !

christophe.bernard-680841

Say Hey Kid

Points: 700
More actions
March 26, 2009 at 3:02 am

#384215

Hi everybody,
i have a problem with a simple select and i don't know how to do it !!!!
here is it my table when i do a simple select :
pk id numero statut majdate
2481 202 0 2009-03-26 08:15:55.00
2482 202 3 2009-03-26 08:35:15.000
2483 202 6 2009-03-26 08:40:12.000
2483 202 9 2009-03-26 08:59:59.020
2484 202 11 2009-03-26 09:05:50.020
AND I WANT ALYWAYS THE VALUE TÖP -1 ROW FOR STATUT !
In my sample it's the value 9
I WANT THE LAST LAST INSERTED VALUE !
Some idea how i can do that ?
Thanks for all
Christophe

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

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

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 1

There are many ways of writing this.

Here's a sample one:

declare @abc int

select @abc=max(statut) from MyTable

select top 1 * from MyTable where statut !=@abc order by statut desc

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 2

Another solution is

SELECT TOP 1 * FROM MyTable

WHERE statut < (SELECT MAX(statut) FROM MyTable)

ORDER BY statut DESC

Regards,
Nitin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

christophe.bernard (3/26/2009)
pk id numero statut majdate 2481 202 0 2009-03-26 08:15:55.00 2482 202 3 2009-03-26 08:35:15.000 2483 202 6 2009-03-26 08:40:12.000 2483 202 9 2009-03-26 08:59:59.020 2484 202 11 2009-03-26 09:05:50.020
AND I WANT ALYWAYS THE VALUE TÖP -1 ROW FOR STATUT !
In my sample it's the value 9
I WANT THE LAST LAST INSERTED VALUE !

What are the rules for choosing the row with a value of 9 for statut? The last inserted row is surely the one with a value of 11 for statut.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 4

Hi,

in fact i must to get the before last inserted value !

here is it the flow, i update with an interface my table and statut can be 0 to 13

0 accept, 1 refuse and so on..

but sometimes i must to replace the statut by the last last (before last) inserted value

in my sample:

pk id numero statut majdate

2480 202 0 2009-03-26 08:15:55.00

2481 202 3 2009-03-26 08:35:15.000

2482 202 6 2009-03-26 08:40:12.000

2483 202 9 2009-03-26 08:59:59.020

2484 202 11 2009-03-26 09:05:50.020

the last value inserted is 11 and the last last inserted value is 9 but 9 can be 0 or 1 or anything else !

IT S ALAWYAS the before last inserted !

Thanks for your time and sorry for my bad english !

christophe

mazzz SSCertifiable Points: 6139 More actions · Answer 5

Something like this could work, assuming "TOP -1" means you want the second from the top!

SELECT ...

FROM

(SELECT TOP 2 ... , ROW_NUMBER() OVER (ORDER BY statut DESC) as rownum)

WHERE rownum = 2

Edited to add DESC to the ORDER BY

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Here's the old-fashioned way:

DROP TABLE #temp

CREATE TABLE #temp ([pk id] INT, numero INT, statut INT, majdate DATETIME)

INSERT INTO #temp

([pk id], numero, statut, majdate)

SELECT 2480, 202, 0, '2009-03-26 08:15:55.00' UNION ALL

SELECT 2481, 202, 3, '2009-03-26 08:35:15.000' UNION ALL

SELECT 2482, 202, 6, '2009-03-26 08:40:12.000' UNION ALL

SELECT 2483, 202, 9, '2009-03-26 08:59:59.020' UNION ALL

SELECT 2484, 202, 11, '2009-03-26 09:05:50.020'

SELECT TOP 1 *

FROM (SELECT TOP 2 *

FROM #temp

ORDER BY statut DESC

) d ORDER BY statut

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Hi christophe

As mazzz already wrote you can handle this with the ROW_NUMBER() function. Try this:

DECLARE @t TABLE (id INT IDENTITY, statut INT)

INSERT INTO @t

SELECT 1

UNION SELECT 3

UNION SELECT 5

UNION SELECT 6

;WITH t_rows (row_num, id) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY statut), id

FROM @t

)

SELECT t.*

FROM @t t

JOIN t_rows ON t.id = t_rows.id

WHERE row_num = (SELECT MAX(row_num) FROM t_rows) - 1

Greets

Flo

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 8

Hi everybody here is it my solution :

SELECT TOP 1 statut as statut1 FROM

(

SELECT TOP 2 statut FROM DESCRIPTION_FLUX WHERE DescriptionNumero = 202

ORDER BY majdate desc

)

test

ORDER BY statut1 asc

thanks for your time, have a nice week end

christophe