Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sort order Expand / Collapse
Author
Message
Posted Friday, April 9, 2010 5:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 749, Visits: 1,300
Hi

How can I get query results in the same order i have given using
'in' express

for example

select col_1,col_2 from table_1
where col_1 in(5,1,3,8,2)

select col_1,col_2 from table_1
where col_1 in(1,3,5,4)

The result should display according the order i have given 'in' order

like
col_1
--------
5
1
3
8
2

Is there any way

Thanks
Padmaja
Post #900359
Posted Friday, April 9, 2010 5:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 6, 2012 5:00 AM
Points: 114, Visits: 74
Hi,
how it is showing output right now ?
Post #900364
Posted Friday, April 9, 2010 5:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
-- Method 1
DROP TABLE #InList
CREATE TABLE #InList (RowID INT IDENTITY(1,1) NOT NULL, JoinValue INT)
INSERT INTO #InList (JoinValue)
SELECT 5 UNION ALL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 8 UNION ALL
SELECT 2
-- NOTE - the sequence will fail if UNION is used instead of UNION ALL
-- because UNION eliminates dupes; the processing reorders the input set.

SELECT col_1,col_2
FROM table_1 t
INNER JOIN #InList i
ON i.JoinValue = t.col_1
ORDER BY i.RowID



-- Method 2
SELECT col_1,col_2
FROM table_1
WHERE col_1 IN (5,1,3,8,2)
ORDER BY CASE col_1
WHEN 5 THEN 1
WHEN 1 THEN 2
WHEN 3 THEN 3
WHEN 8 THEN 4
WHEN 2 THEN 5 END



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #900370
Posted Friday, April 9, 2010 5:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:03 PM
Points: 2,262, Visits: 5,405
Hey padmaja,

how about you going through this following article and helping us help you??

FORUM POSTING ETIQUETTES - JEFF MODEN

When u do so, i am sure a lot of us will help u instantly...
So please post

1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.

Post #900385
Posted Friday, April 9, 2010 6:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 1,059, Visits: 691
Padmaja.

Create a tabular function

which takes input VACHAR(1000)

Returns Table (id identity(1,1),FindNo int)

Write a code to insert the comma seperated string into the table

SELECT * FROM fn_gettblOrder('5,1,3,8,2')


id FIndNo
1 5
2 1
3 3
4 8
5 2


Now u can use Ur query with JOin of this Function....

select Col_1,Col_2 from @table
JOIN (SELECT id,findNo FROM fn_gettblOrder('5,1,3,8,2')) Ftbl on Ftbl.findNo=Col_1
where Col_1 in(5,1,3,8,2) order by id ASC

U may get the O/p what u expected.....& Use this same function where u need to get table from CSV values....


Thanks!
Sasidhar Pulivarthi
Post #900398
Posted Friday, April 9, 2010 7:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 749, Visits: 1,300
Hi Sasidhar,

It really helped me. Thanks

Padmaja[font="Arial"][/font]
Post #900515
Posted Friday, April 9, 2010 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
Sasidhar Pulivarthi (4/9/2010)
Create a tabular function...

Or better still use one of Chris' methods or:
DECLARE @Example
TABLE (
col1 INTEGER NOT NULL PRIMARY KEY,
col2 VARCHAR(10) NOT NULL
);

INSERT @Example
(col1, col2)
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two' UNION ALL
SELECT 3, 'Three' UNION ALL
SELECT 4, 'Four' UNION ALL
SELECT 5, 'Five' UNION ALL
SELECT 6, 'Six' UNION ALL
SELECT 7, 'Seven' UNION ALL
SELECT 8, 'Eight';

SELECT  col1, col2 
FROM @Example
WHERE col1 IN (5,1,3,8,2)
ORDER BY
CHARINDEX
(
',' + CONVERT(VARCHAR(12), col1) + ',',
',' + '5,1,3,8,2' +','
) ASC;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #900552
Posted Friday, April 9, 2010 10:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,728, Visits: 31,180
pmadhavapeddi22 (4/9/2010)
Hi Sasidhar,

It really helped me. Thanks

Padmaja[font="Arial"][/font]


Cool... can you post the function you ended up with, please? I'd like to see what you did. Thanks.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #900975
Posted Tuesday, April 13, 2010 1:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 1,059, Visits: 691

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[Test_GetOrderTable] (@string varchar(max))
returns @tblOrder Table(id int identity(1,1),strings varchar(100))
AS
begin

declare @LocalVar varchar(100)
declare @pos int
set @pos=1
set @LocalVar=''
set @string = ltrim(rtrim(@string))

while (@pos <=len(@string))
begin
if (substring(@string, @pos,1)!=',')
begin
set @LocalVar =@LocalVar + substring(@string,@pos,1)
set @pos=@pos+1
end
else
begin
if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

set @pos= @pos+ 1
set @LocalVar=''
end

end

if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

return
end


GO




Select * from [dbo].[Test_GetOrderTable] ('1,2,8,6,4,23,74,94,4,0')
Post #902168
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse