IN query

[skyseh]

Hi All,

How can I better replace the IN(....) to a better code?

SELECT TOP 1 MIN(table.Item), MIN(table.Post), MIN(table.add), MIN(table.creator), MIN(table.datecreated) FROM table WITH (NOLOCK) WHERE table.code IN('0012','0013','0014').

The above has causes some performance and system run time error issue especially within the IN bracket the table code gets very large. Please advise.

Thanks.

[szlamany]

If this is actual range of codes then:

WHERE table.code Between '0012' and '0014'

But I also cannot understand how that query will run at all - MIN() aggregate functions mixed with TOP 1 makes no sense.

The query should be:

SELECT MIN(table.Item), MIN(table.Post)
, MIN(table.add), MIN(table.creator), MIN(table.datecreated)
FROM table WITH (NOLOCK)
WHERE table.code Between '0012' and '0014'

btw - is table.code a INDEX column on your table? How many rows in the table?

[gigemboy]

Also dont forget that you can nest your Select statements. You can use a select statement to select the numbers you want in your IN statement....

SELECT ... FROM table WHERE Something IN (SELECT IDS From ... Where ... etc).

As long as the nested select contains the data that you are wanting in your IN statement, then it should work. In the above, this means as long as the nested select selects just the numbers...