Showing only the X value in a select query... 1

[CTSQLGUY]

The records I'm displaying have no key, so this makes it harder...

Basically, I have a table that looks like this:

USER-A
USER-B
USER-C
USER-D

I want to be able to display only the third value... any ideas?

I think the best way would be to do a sort and then a select on the third value, I just have no clue how to do it!

Thanks!

 

[gmmastros]

SQL Server 2000 has ranking functions that you can use for this. If this is SQL 2000, then this example should help.

Declare @Temp Table(Data VarChar(100))

Insert INto @Temp Values('USER-A')
Insert INto @Temp Values('USER-B')
Insert INto @Temp Values('USER-C')
Insert INto @Temp Values('USER-D')

Select Top 1 *
From   (
       Select Top 3 Data
       From   @Temp
       Order By Data
       ) As A
Order By A.Data DESC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

I'm trying to execute that and I keep getting:

"The Declare cursor SQL construct or statement is not supported.

 

[gmmastros]

Run this...

Select @@Version

Post the results back here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

Here they are...

------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 1)
------------------------------------------------------

Thanks!

 

[gmmastros]

I was concerned that you were using an older version of sql server. Since you are using sql 2005, the query I show will work if you copy/paste it to a query window. Of course, implementing this in your code may not be so simple. Can you show us the code that you have?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

This is the exact code I'm using...

-----------------------------------------------
SELECT TOP 1 a.companyName
FROM Company AS a INNER JOIN
Support AS b ON a.companyID = b.companyID
WHERE (b.Temperature = 'Red') AND (b.status = 2)
-----------------------------------------------

Thanks!

 

[gmmastros]

Since you are using SQL 2005....

SELECT a.companyName, Row_Number() Over(Order By A.CompanyName) As RowId
FROM   Company AS a INNER JOIN
         Support AS b ON a.companyID = b.companyID
WHERE  (b.Temperature = 'Red') AND (b.status = 2)

If you want to return a particular row, you could use...

Select CompanyName
From   (
       SELECT a.companyName, 
              Row_Number() Over(Order By A.CompanyName) As RowId
       FROM   Company AS a INNER JOIN
                Support AS b ON a.companyID = b.companyID
       WHERE  (b.Temperature = 'Red') AND (b.status = 2)
       ) As A
Where  A.RowId = 3

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

Hrmmm.... I keep getting the message:

--------------------------------------------------------
The OVER SQL construct or statement is not supported.
--------------------------------------------------------

Sorry for the trouble... thanks for the help though!

 

[CTSQLGUY]

Any ideas...?

 

[gmmastros]

I assume you are using the management studio. So, do this for me.

right click your database
click properties
on the left, click options

What is the compatibility level set to?

Whatever you do, don't change this value because it could cause other things to fail.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

Actually I got it working using a different method.

Thanks for the help and quick responses though, you pointed me in the right direction!

 

[gmmastros]

Could you please post what that 'other method' is? It may come in handy for others that have the same problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CTSQLGUY]

Instead of creating a temp table as you had, I ended up creating a perm table with counters.

It's basically your method but now there is a key.