how to get some records in first row in sql

Question

I have q query like this:

 Select  WarehouseCode  from [tbl_VW_Epicor_Warehse]

my output looks like this

WarehouseCode
Main
Mfg
SP
W01
W02
W03
W04
W05

But sometimes I want to get W04 as the first record, sometimes I want to get W01 as the first record .

How I can write a query to get some records in first row??
Any help is appreciated

You can use CASE statement with ORDER BY Clause to achieve same. — Ankit Bajpai, Jan 19 '16 at 11:17

score 1 · Answer 1 · answered Jan 19 '16 at 11:27

Use a parameter to choose the top row, which can be passed to your query as required, and sort by a column calculated on whether the value matches the parameter; something like the ORDER BY clause in the following:

DECLARE @Warehouses TABLE (Id INT NOT NULL, Label VARCHAR(3))

INSERT @Warehouses VALUES
 (1,'W01')
,(2,'W02')
,(3,'W03')

DECLARE @TopRow VARCHAR(3) = 'W02'

SELECT *
FROM @Warehouses
ORDER BY CASE Label WHEN @TopRow THEN 1 ELSE 0 END DESC

score 1 · Accepted Answer · answered Jan 19 '16 at 11:29

1

you could try and select the row with the code you want to appear first by specifying a where condition to select that row alone then you can union all another select with all other rows that doesn't have this name

as follows

SELECT WarehouseCode FROM Table WHERE WarehouseCode ='W04'
UNION ALL 
SELECT WarehouseCode FROM Table WHERE WarehouseCode <>'W04'

answered Jan 19 '16 at 11:29

Maha Khairy

352
5
15

1

http://stackoverflow.com/questions/15766359/does-union-all-guarantee-the-order-of-the-result-set – Neeraj Prasad Sharma Jan 19 '16 at 11:44
i think this will work. you can pass first item as a parameter. – Sahi Jan 19 '16 at 11:49
Dear @jasjas but its not guarantee it will work always, most of the time YES it will work – Neeraj Prasad Sharma Jan 20 '16 at 11:56
dear @NeerajPrasadSharma I'm curious about the cases when this might not work, could you please elaborate – Maha Khairy Jan 20 '16 at 13:26
Dear @MahaKhairy please see my EDITED answer please – Neeraj Prasad Sharma Jan 25 '16 at 06:19

score 0 · Answer 3 · edited May 23 '17 at 12:23

As commented by @ankit bajpai

You are looking for Custom sorting that is achieve by CASE with ORDER BY statement

Whenever you want WAo4 on top you can use

 ORDER BY Case When col = 'W04'  THEN 1 ELSE 2 END

Example below:

Select col from 
(
select 'Main' col  union ALL
select  'Mfg' union ALL
select  'SP' union ALL
select  'W01' union ALL
select  'W02' union ALL
select  'W03' union ALL
select  'W04' union ALL
select  'W05' 
) randomtable 

ORDER BY Case When col = 'W04'  THEN 1 ELSE 2 END

EDIT: AFTER MARKED AS ANSWER

IN support of @Maha Khairy because that IS MARKED AS ANSWER and the only answer which is DIFFRENT rest all are pushing OP to use "ORDER by with case statements"

let`s use UNION ALL APPROCH

create table #testtable (somedata  varchar(10))

insert into #testtable

Select col from 
(
 select  'W05'  col union ALL
select 'Main'   union ALL
select  'Mfg' union ALL
select  'SP' union ALL
select  'W01' union ALL
select  'W02' union ALL
select  'W03' union ALL
select  'W04'

) randomtable 


Select * From #testtable where somedata = 'W04'
Union ALL
Select * From #testtable where somedata <> 'W04'

The result set is rendering data to the grid as requested the OP

idia is to get first all rows where equal to 'W04' is and then not equal to 'W04' and then concatinate the result. so that rows 'W04' will always be on the top because its used in the query first, fair enough.

, but that is not the only point to use (custom sorting/sorting) in that fasion there is one another and a major one that is PERFORMANCE yes "case with order by" will never able to take advantages of KEY but Union ALL will be, to explore it more buld the test table and check the diffrence

CREATE TABLE  #Orders
(  
    OrderID    integer  NOT NULL IDENTITY(1,1),
    CustID     integer  NOT NULL,
    StoreID    integer  NOT NULL,
    Amount     float    NOT NULL,

    makesrowfat nchar(4000)
);
GO
-- Sample data
WITH  
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows  
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows  
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows 
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows 
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows 
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows 
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)


INSERT  #Orders 
    (CustID, StoreID, Amount)
SELECT 
    CustID  = Number / 10,
    StoreID = Number % 4,
    Amount  = 1000 * RAND(Number)
FROM FinalCte
WHERE 
    Number <= 1000000;
GO

lets now do the same for custid "93190"

Create NONclustered Index IX_CustID_Orders ON #Orders (CustID)
INCLUDE (OrderID ,StoreID, Amount ,makesrowfat    )

WARM CHACHE RESULTS

SET STATISTICS TIME ON

 DECLARE @OrderID     integer 
 DECLARE @CustID      integer 
 DECLARE @StoreID     integer 
 DECLARE @Amount      float   
 DECLARE @makesrowfat nchar(4000)


Select  @OrderID    =OrderID ,   
        @CustID     =CustID    , 
        @StoreID    =StoreID    ,
        @Amount     =Amount     ,
        @makesrowfat=makesrowfat

        FROM
(
Select * From  #Orders where custid =93190
Union ALL
Select * From  #Orders where custid <>93190
)TLB

**

--elapsed time =2571 ms.

**

 DECLARE @OrderID     integer 
 DECLARE @CustID      integer 
 DECLARE @StoreID     integer 
 DECLARE @Amount      float   
 DECLARE @makesrowfat nchar(4000)

 Select  @OrderID    =OrderID ,   
        @CustID     =CustID    , 
        @StoreID    =StoreID    ,
        @Amount     =Amount     ,
        @makesrowfat=makesrowfat

        From  #Orders

 ORDER BY Case When custid = 93190  THEN 1 ELSE 2 END

elapsed time = 70616 ms

**

UNION ALL performance 2571 ms. ORDER BY CASE performance 70616 ms

**

UNION ALL is a clear winner ORDER BY IS not ever nearby in performance

BUT we forgot that SQL is declarative language, we have no direct control over how data has fetch by the sql, there is a software code ( that changes with the releases) IN between user and sql server database engine, which is SQL SERVER OPTIMIZER that is coded to get the data set as specified by the USER and its has responsibility to get the data with least amount of resources. so there are chances that you wont get ALWAYS the result in order until you specify ORDER BY

some other references:

@Damien_The_Unbeliever Why would anyone offer an ordering guarantee except when an ORDER BY clause is included? - there's an obvious opportunity for parallelism (if sufficient resources are available) to compute each result set in parallel and serve each result row (from the parallel queries) to the client in whatever order each individual result row becomes available. –

Conor Cunningham: If you need order in your query results, put in an ORDER BY. It's that simple. Anything else is like riding in a car without a seatbelt.

ALL COMMENTS AND EDIT ARE WELCOME

score 0 · Answer 4 · answered Jan 19 '16 at 11:35

May be you don't need to store this list in table? And you want something like this?

SELECT * FROM (VALUES ('WarehouseCode'),
                        ('Main'),
                        ('Mfg'),
                        ('SP'),
                        ('W01'),
                        ('W02'),
                        ('W03'),
                        ('W04'),
                        ('W05')) as v(s)

Here you can change order manually as you want.

how to get some records in first row in sql

4 Answers4