one less than max id

[simma]

Hello,
how do I select a record one less than max records for example

id num
23 456009
45 456009
67 456009

I want to select 45..

 

[SQLBill]

Try:

SELECT MAX([id]),
        num
FROM tablename
WHERE [id] < (SELECT MAX(id) FROM tablename)

-SQLBill

Posting advice: FAQ481-4875

 

[simma]

Thanks Sqlbill,
its returning 45 and 23..I need only 45

 

[SQLBill]

Does your query start with SELECT MAX([id])???? If you leave the MAX out, it won't work.

-SQLBill

Posting advice: FAQ481-4875

 

[chaoma]

I think this will work.

SELECT MAX(T.[ID]), T.NUM FROM
(SELECT [ID], NUM FROM MYTABLE WHERE [ID] NOT IN (SELECT MAX([ID]) FROM MYTABLE)) as T
Group by t.num

 

[swampBoogie]

select id,num
from t
where id = (
  select max(id) 
    from t
   where id < (
     select max(id)
       from t))

 

[simma]

Thanks SQLBill,chaoma,swampBoogie !!

 

[vongrunt]

No MAX()" method:

SELECT TOP 1 *
FROM
(	SELECT TOP 2 *
	FROM blah
	ORDER BY id DESC
) whatever
ORDER BY id ASC

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[maswien]

When I want post my code, I found I wrote the exactly same code as vongrunt, I think this is definate another good way to go.

 

[chaoma]

Vongrunt,

Your query is impressive! Very simple.