question

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 asked GaryKeramidas-2715 answered

can results be in order of IN keyword condtions?

i'm using item numbers for the IN conditions to return a total of each item number ordered for sales orders between 2 dates. i need the results to be in the same order the item numbers appear in the IN statement. the item numbers in the IN statement are not sorted in any way.
is this possible?
if i use a date range i get a different order of results than if i use no date range and just the item numbers. but, neither is correct.


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

when i look at the tempdb database, there is a table dbo#tbl but it has numerous underscore characters follwed by 00000000000A as the table name


This is because when you say CREATE TABLE #temp, SQL Server tacks on all these underscores and these hex digits to make the name unique, so that to two processes can say CREATE TABLE #temp without interfering with each other. But that is something that happens under the covers, and you don't really have to bother.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

To get the results in a specific order you must include an ORDER BY on the outer most query. If the values in the IN statement have no defined order - there isn't any way to build an ORDER BY statement to match.

With that said - how are you getting the values in the IN statement? Either those are hard-coded when you wrote the query - or you are using SELECT to return the list of item numbers to be included or dynamic SQL. If you are using dynamic SQL to build a SQL statement from a delimited string then there is a way this can be done - but it requires changing the query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 answered

thanks for your response.

they're hard coded, built from a list of items in a spreadsheet.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

So if you have

col IN ('Pineapple', 'Banana', 'Orange', 'Apple')

you need to add, one way or another

ORDER BY CASE col WHEN 'Pineapple' THEN 1
                  WHEN 'Banana' THEN 2
                  WHEN 'Orange' THEN 3
                  WHEN 'Apple' THEN 4
        END

Alternatively, you insert the data to a two column table:

INSERT @tbl(Data, No)
   VALUES('Pineapple', 1),
         ('Banana', 2),
         ('Orange', 3),
         ('Apple', 4)

and then you write the query as:

JOIN @tbl t ON col = t.Data
...
ORDER BY t.No
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 answered

ErlandSommarskog

thanks for your ideas. i can give them a try.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 answered

your case statement in the order by dies seem to work. but i have other issues. it only returns items with order in the sales order table an i need to include the ones tn the list that do not have any sales records, too.

so, i guess it won't work.

thanks for your suggestions, i did learn something.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

an i need to include the ones tn the list that do not have any sales records, too.

so, i guess it won't work.

Au contraire, that's when you really need that table.

Please keep in mind that since I don't know your tables, I can only post an outline, but it would be something like this:

SELECT t.Data, SO.col1, SOl.col2, ...
FROM  @tbl t
LEFT JOIN SalesOrder SO ON t.Data = SO.item
ORDER  BY t.no
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 answered

thanks again. and that's what i figured, but i have never done that so i'm hesitant to try it. i don't want to mess up any of the client's data. i'm guessing this won't, but lack of knowledge about this approach scares me.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaryKeramidas-2715 avatar image
0 Votes"
GaryKeramidas-2715 answered

here's what i tried on my sql test server:
create table tbl(data varchar(10),no numeric(3) )
INSERT into tbl(Data, No)
VALUES('Pineapple', 1),
('Banana', 2),
('Orange', 3),
('Apple', 4)

select * from tbl

query worked but created a physical table i then had to drop

i tried using "@tbl" like in your example, wouldn't work, tells me i have to declare a variable, don't know how.

i then tried the #tbl. it says it was successful creating the table but trying a query says table not found.

i just don't know enough on how to do this.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

To declare a table variable:

DECLARE @tbl TABLE (Data varchar(30), no int PRIMARY KEY)
INSERT @tbl(Data, no) VALUES ...

It's more or less the same as a temp table, but a slightly different syntax.

i then tried the #tbl. it says it was successful creating the table but trying a query says table not found.

Sounds like a spelling error to me. (But I don't see your code.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.