SQL help

[bonho]

Sorry this is not entirely C++, though it is a part of my C++ program to retrieve data from database.

The program needs to select the top 1000 rows from a table, ordered by a 'lump sum' field. However, I also need a few custom selected rows, specified by a list of ID's which differed every day. While this list can be read from a text file, their 'lump sum' might be too small to be included in the top 1000 search. I am trying to avoid using "UNION" because it became rather complex to join two SQL statements and I'll need to update that "TOP X" count. Is there a smarter way to get around this, hopefully with one SQL statement?

************************************************************
DECLARE @last_date DATE
SET @last_date = (SELECT MAX(update_date) FROM MyTable)

SELECT TOP 1000
ID, Value, LumpSum
FROM MyTable
WHERE
update_date = @last_date AND
ID in (100,101,102,103)
ORDER BY LumpSum
************************************************************

where the list 100,101,102,103 are custom generated everyday

[jonnin]

the easiest thing to do, since you are doing this in a program, is simply to do 2 sql statements.

SELECT TOP 1000
ID, Value, LumpSum
FROM MyTable
WHERE
update_date = @last_date

combine that result with

SELECT ID, Value, LumpSum
FROM MyTable
WHERE
update_date = @last_date and
ID in (100,101,102,103)

and sort your result via lump-sum afterwards. Optionally remove any duplicates. I think you may need the order by on the first one, actually .. it may effect what rows come back, not sure.

The only other way I know of would be a union or union all statement, which should not be that difficult.