Select ALL where certain rows returns first? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select ALL where certain rows returns first?

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-20 : 13:07:54

hi friends!

this might be a silly question, but is there someway i can do a

SELECT *
WHERE something LIKE something

then have a certain row be first in the list returned. like return ID=5 as the first row, then i dont care...

thanks a lot for any help friends!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 13:15:26

its possible. something like


SELECT <all columns except OrdVal here>
FROM
(
SELECT *,CASE WHEN returnID=5 THEN 1 ELSE 0 END AS OrdVal
WHERE something LIKE something
)t
ORDER BY OrdVal DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-20 : 15:21:21

thanks for the reply! im not having much luck

here is my regular select


SELECT TOP 1000 [uid]
      ,[fcOrderID]
      ,[p21OrderNo]
      ,[Carrier]
      ,[dateShipped]
      ,[PROTrackingNo]
      ,[LoadNo]
      ,[CarrierInvNo]
      ,[dateInvoice]
      ,[InvAmnt]
      ,[InvTTL]
      ,[Delete]
  FROM [Server].[dbo].[TableName]

I want to select * where LoadNo = Something and Have a particular fcOrderID be the one on top or the first record returned?

any more assistance is greatly appreciated!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-20 : 15:26:02

What did you try? Just implementing Visakh's solution, this is what your SELECT should look like..

SELECT TOP 1000 [uid]
      ,[fcOrderID]
      ,[p21OrderNo]
      ,[Carrier]
      ,[dateShipped]
      ,[PROTrackingNo]
      ,[LoadNo]
      ,[CarrierInvNo]
      ,[dateInvoice]
      ,[InvAmnt]
      ,[InvTTL]
      ,[Delete]
FROM
(
SELECT [uid]
      ,[fcOrderID]
      ,[p21OrderNo]
      ,[Carrier]
      ,[dateShipped]
      ,[PROTrackingNo]
      ,[LoadNo]
      ,[CarrierInvNo]
      ,[dateInvoice]
      ,[InvAmnt]
      ,[InvTTL]
      ,[Delete]
	  ,CASE WHEN [fcOrderID] = 5 THEN 1 ELSE 0 END AS [OrdVal]
  FROM [Server].[dbo].[TableName]
) T
ORDER BY [OrdVal] DESC

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-20 : 15:30:56

ah yes perfect, i just had a mix up in the query.

that works EXACTLY as i was hoping!

Thanks so much for the replys, you guys are the BEST!

SQLTEAM!

Later Friends!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:27:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources