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: 4770 More actions · Answer 1

SQL_DBA_3

SSCarpal Tunnel

Points: 4770

March 26, 2009 at 3:13 am

#966477

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

NitinPatel31

Hall of Fame

Points: 3721

March 26, 2009 at 3:23 am

#966483

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: 37299 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