?
Solved

Finding the nth value in a colunm in t-sql

Posted on 2007-07-31
6
Medium Priority
?
812 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:Putoch
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19600370
It is returning the correct values...
0
 
LVL 10

Expert Comment

by:ksaul
ID: 19600630
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

0
 

Author Comment

by:Putoch
ID: 19601521
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Accepted Solution

by:
ksaul earned 375 total points
ID: 19601620
sorry - forgot to change the table name back to yours:

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

Author Comment

by:Putoch
ID: 19602140
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
0
 

Author Comment

by:Putoch
ID: 19608621
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

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question