Top 2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top 2

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 06:49:56

i have this statement I want the top 2 (RFR desc) be the first in the list.

SELECT symptom,ResolutionID, ResolutionTitle, PhoneBrand,ResolutionDetails1, case when (resolution_A)*1.0 = '0' then '0' else (resolution)*1.0/(resolution_A) *1.0 end AS RFR
FROM dbo.GlobeKB_KMT3
WHERE Symptom = MMColParam
ORDER BY RFR desc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:01:13

not clear on what you are asking.. may be this ???

SELECT top 2
	symptom,ResolutionID, ResolutionTitle, PhoneBrand,ResolutionDetails1, 
	case when (resolution_A)*1.0 = '0' then '0' else (resolution)*1.0/(resolution_A) *1.0 end AS RFR
FROM 
	dbo.GlobeKB_KMT3
WHERE 
	Symptom = MMColParam
ORDER BY 
	case when (resolution_A)*1.0 = '0' then '0' else (resolution)*1.0/(resolution_A) *1.0 end  
	desc

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 07:02:40

Is it possible that I can add another column that numbers it from 1 to 10 (depending on number of rows

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:06:18

using ROW_NUMBER() function if you are on SQL 2005

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 07:08:24

I'm using 2000. is that still possible?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:09:08

if you are on sql 2000, you would have to insert into a temp table using identity function to generate the extra column you need and then run a select from that temp table. Or, you could combine these steps in a function which returns a table.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 07:11:22

how's is that?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:13:32

A small example here. You can try fitting your select in here too,,

CREATE FUNCTION dbo.ufntest(@var varchar(10))
RETURNS @Information TABLE 
(
    -- Columns returned by the function
    col1  nvarchar(1000) NULL, 
    col2 int  IDENTITY(1,1)
)
AS 

BEGIN
  
	insert into @information(col1)
		select  Top 2 
			...
			...
		from
			...
		where 
			..
		order by
			 ..
  
    RETURN
END

Makes sense ?

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 07:18:26

I didn't get it..but the number of rows is a dynamic. Which means it's not constant.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:31:23

thats fine. Rmove the top 2. All the rows would get numbered.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-31 : 07:40:06

I'm getting this error: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:01:58

can you post whatever you are trying ?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-31 : 08:02:04

yes this error because the columns field in your table table and the values u tried to insert in that table does not matches,,,u can take a look at that ,,,,
or post your full query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources