asked on

Finding the nth value in a colunm in t-sql

Hi,
This is really confusing me, i have been looking at different solutions and i have been looking up stuff but i can't see how the code below will not work.
I have created the table and inserts and the select statement i am using, I just want to find the 2nd largest id in this table. this is the example below:

Create table testtable (
Account_id nvarchar(10),
colunm_id int not null)

INSERT INTO [testtable]values (75004,19323);
INSERT INTO [testtable]values (75004,215459);
INSERT INTO [testtable]values (75004,217988);
INSERT INTO [testtable]values (75004,365446);
INSERT INTO [testtable]values (75004,368770);

SELECT a.colunm_id
from testtable a
INNER JOIN (Select max(b.colunm_ID)maxid
from testtable b
--this will choose the max id in the table and the query above will pick the next value.
where b.colunm_id not in
(select top 1 c.colunm_id
from testtable c
where c.account_id = b.account_id
order by c.colunm_id desc))
subq on subq.maxid = a.colunm_id
where a.account_id = 75004

However if i insert these exaples instead
INSERT INTO [testtable]values (123456,19323);
INSERT INTO [testtable]values (123456,45824);
INSERT INTO [testtable]values (123456,859785);
INSERT INTO [testtable]values (123456,444666);
INSERT INTO [testtable]values (123456,598752);

and find the second largest id for 123456 it works??? what am i doing wrong please any advise would be appriciated its driving me mad!
Kind Regards,
Elaine.

Aneesh

It is returning the correct values...

ksaul

I think this would be a more straight-forward way to do the same thing:

SELECT Max(colunm_id)
FROM testtable a
WHERE colunm_id < (SELECT Max(colunm_id) from name where account_id= a.account_id)
and a.account_id = 75004

Putoch

ASKER

Thanks for your comments folks,
Aneeshattingal, have you inserted both inserts? and it still works? because it does not for me for some reason? i can not understand this.

ksaul, I am getting the following error when i run your code:
'Invalid object name 'name'.' is this code running fine for you? i am using SQL SERVER 2005

Thanks for you help guys

ASKER CERTIFIED SOLUTION

ksaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Putoch

ASKER

My God i never even seen the change in the table name i am going blind lookin at this, i know its simple code but that was what was annoying me.
Sorry for that.
Ok i see this works great with just the two colunm but if i add in any more fields it is bringing back all the entries!
for example if i drop the table angain and add in more field names:
Drop table testtable

Create table testtable (
Account_id nvarchar(10),
colunm_id int not null,
status_id smallint not null,
status_date datetime not null)

INSERT INTO [testtable]values (75004,19323,1,'2006-04-29 12:48:37.000');
INSERT INTO [testtable]values (75004,215459,2,'2006-12-13 11:37:42.000');
INSERT INTO [testtable]values (75004,217988,1,'2006-12-15 13:46:10.000');
INSERT INTO [testtable]values (75004,365446,2,'2007-06-20 08:59:30.000');
INSERT INTO [testtable]values (75004,368770,1,'2007-06-25 11:22:07.000');
Now if i run this Statement now this works as i want it to:
SELECT * FROM testtable a
INNER JOIN (SELECT MAX(b.colunm_ID)maxid
FROM testtable b
--this will choose the max id in the table and the query above will pick the next value.
WHERE b.colunm_id NOT IN
(SELET TOP 1 c.colunm_id
FROM testtable c
WHERE c.account_id = b.account_id
ORDER BY c.colunm_id desc))
subq on subq.maxid = a.colunm_id
WHERE a.account_id = 75004

BUT if i now insert these rows and run the same statement again it will return noting!
INSERT INTO [testtable]values (123456,19323,1,'2007-04-29 12:48:37.000');
INSERT INTO [testtable]values (123456,45824,2,'2007-04-29 12:48:37.000');
INSERT INTO [testtable]values (123456,859785,1,'2006-04-29 12:48:37.000');
INSERT INTO [testtable]values (123456,444666,2,'2006-04-29 12:48:37.000');
INSERT INTO [testtable]values (123456,598752,1,'2005-04-29 12:48:37.000');

AND if I use the statement you posted for me Ksaul and add in the extra fields i want to see i end up returning all the rows in the table rahther then just the second highest id and its occurances.

SELECT Max(colunm_id), account_id, status_id, status_date
FROM testtable a
WHERE colunm_id < (SELECT Max(colunm_id) from testtable where account_id= a.account_id)
and a.account_id = 75004
Group by account_id, status_id, status_date

Sorry i have tried various waus of running this and i either get an error back or all the row!!
Thanks,
Putoch

Putoch

ASKER

Ok i was looking at Row_number and some other new functions in SQL SERVER 2005 ( RANK, DENSE_RANK, and NTILE) however , I was reading a book and i came across a quote saying ''you can't use the ROW_NUMBER() (or any of the other ranking functions) in the WHERE clause'' but advised me to Create a View instead. So i
did a Select statement using Row_number to get an extra column to rank each account_id
then created a view on this statement and i can do a select on the view where ranking = 2!

--Adding in Rank to table
SELECT Account_id, status_date, status_id, colunm_id,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY colunm_id DESC) AS RANKING
FROM testtable AS tab

--Create View
create view vwtab as
SELECT Account_id, status_date, status_id, colunm_id,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY colunm_id DESC) AS RANKING
FROM testtable AS tab

--Statement
select * from vwtab where RANKING = 2

So this will allow me to see all account_id and their second highest input into the table.
Thank you for your time and help
Putoch