troubleshooting Question

Finding the nth value in a colunm in t-sql

Avatar of Putoch
PutochFlag for Afghanistan asked on
DB Reporting ToolsMicrosoft SQL Server 2005SQL
6 Comments1 Solution852 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
ksaul

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros