sql server programming:
SQL does not sort the data. The order you get the data back is only luck (well, more than luck, but it does not have to return them in this way. I assume you have a clustered index on the PID column.) If SQL Server wants to return it in any order that would be perfectly OK. If you want a particular order, use an ORDER BY clause. Adam's post gives you a method of doing what you want. -- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) [quoted text, click to view] "Guy Brom" <guy_brom@yahoo.com> wrote in message
news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl...
> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause.
>
> Something like:
>
> SELECT * FROM Products
> WHERE pid IN (5,1,2,309,171)
>
> SQL uses the default sorting column (in this case I belive it's the pid,
> which is also the PK).
>
> Is it possible to maintain the sorting order similiar to the IN digits?
>
>
You could do something like this: SELECT * FROM Products WHERE pid IN (5,1,2,309,171) ORDER BY CASE pid WHEN 5 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 309 THEN 4 WHEN 171 THEN 5 ELSE 6 END [quoted text, click to view] "Guy Brom" <guy_brom@yahoo.com> wrote in message
news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl...
> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause.
>
> Something like:
>
> SELECT * FROM Products
> WHERE pid IN (5,1,2,309,171)
>
> SQL uses the default sorting column (in this case I belive it's the pid,
> which is also the PK).
>
> Is it possible to maintain the sorting order similiar to the IN digits?
>
>
Unless someone else comes up with a better solution. If you are using this inside a stored procedure: Create a temp table with 2 columns. Loop through the comma delimited list, inserting the values into the first column and and increment integers into the second. That is: col1 col2 5 1 1 2 2 3 309 4 171 5 Join on col1 from your query, ordering on col2. [quoted text, click to view] "Guy Brom" <guy_brom@yahoo.com> wrote in message
news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl...
> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause.
>
> Something like:
>
> SELECT * FROM Products
> WHERE pid IN (5,1,2,309,171)
>
> SQL uses the default sorting column (in this case I belive it's the pid,
> which is also the PK).
>
> Is it possible to maintain the sorting order similiar to the IN digits?
>
>
groups.google.com/groups?selm=2869101c46445%24d8673520%24a401280a%40phx.gbl
--
Anith
I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause.
Something like:
SELECT * FROM Products
WHERE pid IN (5,1,2,309,171)
SQL uses the default sorting column (in this case I belive it's the pid,
which is also the PK).
Is it possible to maintain the sorting order similiar to the IN digits?
Guy, You can order by the position of pid within your in_list: order by charindex(','+cast(pid as varchar(10))+',', ','+@inlist+',') Cheers, -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Guy Brom" <guy_brom@yahoo.com> wrote in message
news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl...
> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause.
>
> Something like:
>
> SELECT * FROM Products
> WHERE pid IN (5,1,2,309,171)
>
> SQL uses the default sorting column (in this case I belive it's the pid,
> which is also the PK).
>
> Is it possible to maintain the sorting order similiar to the IN digits?
>
>
Thanks Itzik!! That worked. Are there any drawbacks for this method? my @inlinst usually holds about 20 digits. [quoted text, click to view] "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message news:OjMjHFWhEHA.3348@TK2MSFTNGP12.phx.gbl... > Guy, > > You can order by the position of pid within your in_list: > > order by charindex(','+cast(pid as varchar(10))+',', ','+@inlist+',') > > Cheers, > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Guy Brom" <guy_brom@yahoo.com> wrote in message > news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl... >> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause. >> >> Something like: >> >> SELECT * FROM Products >> WHERE pid IN (5,1,2,309,171) >> >> SQL uses the default sorting column (in this case I belive it's the pid, >> which is also the PK). >> >> Is it possible to maintain the sorting order similiar to the IN digits? >> >> > >
Not that I know of. Neither this method nor the CASE expression can utilize an index since both are manipulations rather than a base column. So, both should yield similar performance. -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Guy Brom" <guy_brom@yahoo.com> wrote in message news:%237od7LchEHA.3632@TK2MSFTNGP09.phx.gbl... > Thanks Itzik!! That worked. > Are there any drawbacks for this method? my @inlinst usually holds about 20 > digits. > > "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message > news:OjMjHFWhEHA.3348@TK2MSFTNGP12.phx.gbl... > > Guy, > > > > You can order by the position of pid within your in_list: > > > > order by charindex(','+cast(pid as varchar(10))+',', ','+@inlist+',') > > > > Cheers, > > -- > > BG, SQL Server MVP > > www.SolidQualityLearning.com > > > > > > "Guy Brom" <guy_brom@yahoo.com> wrote in message > > news:eNd43oUhEHA.3148@TK2MSFTNGP10.phx.gbl... > >> I'm passing a varchar list of digits (5,1,2,309,171) to a WHERE clause. > >> > >> Something like: > >> > >> SELECT * FROM Products > >> WHERE pid IN (5,1,2,309,171) > >> > >> SQL uses the default sorting column (in this case I belive it's the pid, > >> which is also the PK). > >> > >> Is it possible to maintain the sorting order similiar to the IN digits? > >> > >> > > > > > >
Don't see what you're looking for? Try a search.
|
