Select First - 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

Select First

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2009-08-28 : 07:05:06

I have a number of records in a table.
I want to selct all the recordss as they are going into a drop down list.
I want to be able to have a specific record to be at the top of the select statement based on the record ID that I will pass into the query.
Anyone know how to achieve this?
macca

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-28 : 07:15:33

Hi

use dynamic sql
DECLARE @SQL VARCHAR(MAX)
DECLARE @TOP VARCHAR(50)
SET @SQL = ' SELECT TOP '+@TOP+' * FROM TABLE '
EXEC SP_EXECUTESQL @sql

-------------------------
R...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-28 : 07:25:26

No, don't use dynamic SQL:

CREATE PROCEDURE DropDown @id int AS
SET NOCOUNT ON
SELECT myColumn FROM myTable
ORDER BY CASE WHEN ID=@id THEN 0 ELSE 1 END, ID

Syntax:  EXEC DropDown 10  -- will sort ID 10 at top of list

Your requirements aren't very clear, if this does not work for you you'll have to post more details on your tables, including sample data and expected results.

macca
Posting Yak Master

146 Posts

Posted - 2009-08-28 : 08:47:08

Here is the code I am attempting to use:

CREATE PROCEDURE sproc_GetReceivedMethod
(
@SubId int
)

AS

SELECT RecMethod, RecId

FROM Received

ORDER BY CASE WHEN Reference = @SubId

I want to select all records but have whichever record has the Reference = @SubId to be at the top of the list

macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 08:55:38

You didnt apply what was suggested

CREATE PROCEDURE sproc_GetReceivedMethod
(
@SubId int
)

AS

SELECT RecMethod, RecId

FROM Received

ORDER BY CASE WHEN Reference = @SubId then THEN 0 ELSE 1 END, Reference

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

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

Try this too

select * from
(select row_number() over(order by your_column) as s_no,
your_column from table_name) t
where s_no <= @top

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 09:08:48

quote:
Originally posted by senthil_nagore

Try this too

select * from
(select row_number() over(order by your_column) as s_no,
your_column from table_name) t
where s_no <= @top

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This is not what OP wants

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2009-08-28 : 09:27:31

Thanks for all your help guys, especially Madhivnan.
Got that working.
macca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources