SELECT TOP 12 Skips one!

[David Lozzi]

Howdy,

Below is my SELECT statement. The returned list is suppose to return the TOP
12 when sorted by MIN(dtDate). It sorts the records correctly and only
displays 12 records, but to meet the 12 record criteria, it removes a record
that should be #6. If I change the 12 to a 13, that missing record appears,
however now its 13 and not 12. The UNION is to add a blank record at the end
so i can capture the last record in my DataList to display something
special, which works great. If I remove the UNION statement and keep it at
TOP 12, it works properly, it selects the top 12. Is there something I'm
missing on this UNION???

Thanks!!
(SELECT TOP 12
dbo.tblEvents.dtDisplayUntil, dbo.tblEvents.ID, MIN(dbo.tblDates.dtDate) as
dtDate, dbo.tblEvents.strTitle,
strDescription AS Description,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
FROM dbo.tblEvents LEFT OUTER JOIN
dbo.tblDates ON dbo.tblEvents.ID = dbo.tblDates.CID
GROUP BY dbo.tblEvents.ID, dbo.tblEvents.dtDisplayUntil,
dbo.tblEvents.strTitle, dbo.tblEvents.strDescription,
dbo.tblEvents.strLocation, dbo.tblEvents.strCost
HAVING (dbo.tblEvents.dtDisplayUntil >= GETDATE()))
UNION
SELECT GETDATE() + 360 as dtDisplayUntil, 0 as ID, GETDATE() + 360 as
dtDate, '' as strTitle, '' as description, '' as strLocation, '' as strcost
ORDER BY MIN(dbo.tblDates.dtDate)

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

[Steven Cheng[MSFT]]

Hi David

Thanks for your posting.
From your description, I understand that one of your asp.net page use
ADO.NET code to query top 12 rows from the a certain table and add an extra
row by using the UNION clause; the expected result is 13 rows. However, you
only get 12 rows actually, correct? If there is anything I misunderstand,
please feel free to let me know.

To simplified the problem for testing, I create a table with two varchar
columns, and test a simple SQL with UNION clause, unfortunately I haven't
been able to reproduce the problem through the test tables, it just returns
the 13 rows as expected. So the problem might not about the UNION clause,
to separate the issue, I suggest you test the first SQL statement without
the UNION clause, to confirm whether it is statement-specific problem or
not. Also, you can try testing the same UNION logic through some other
tables to verify my test result.

BTW, since this is likely a pure T-SQL issue, I also suggest you try
posting in SQLServer or any other DB related newsgroup(ADO.NET) to see
whether there're any other members can help.

If you have any further information related to this problem, please feel
free to post here.
Thanks very much and looking forward to hearing from you.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)