I need help with this select query...

[wyrd]

EDIT: Solved, read next post.

Im writing a paging mechanism in a database program, and am having a problem with paging one of my tables. The table has the following columns;

id: int
date: smalldatetime
description: varchar(30)

date and description are a clustered index, id is primary and identity column. In my program Im sorting by date, description in ascending order.

Im coming to a problem because the program is inserting dates like so;
5/1/2003
5/1/2003
6/1/2004

Notice something common? Theyre all on the 1st day of the month. This is as intended, and this is not the problem but directly related to the problem.

Bare with me, Im giving you all this info for a reason, and here is that reason; As I mentioned above, Im adding a paging mechanism to my program where each page contains 100 records. The problem is the date. You see, if on record # 90-110 the date column contains 5/1/2003 for example (which is a common occurance), then I have no way to determine which of those rows would be for the 1st or 2nd page, except by using order by description (luckily this is what I wanted anyway). HOWEVER, that in itself is causing a problem.

Heres an example of the query I first tried
($vardate = date from program of last item in previous page)
($vardesc = description from program of last item in previous page)
($varid = id from program of last item in previous page)

SELECT TOP 100 * FROM items WHERE DATEDIFF(day, $vardate, date) >= 0 AND description >= $vardesc AND id != $varid

At first glance this seemed like itd work easily enough. Unfortunately it doesnt. As you might have guessed, it skips over items with a description that is < $vardesc regardless of date. If I switched it to OR then it would obviously display dates I didnt want as itd match anything from description >= $vardesc. And I cant use WITH TIES either, because there could be 10,000 records for a specific month and year, and that would defeat the purpose of paging.

Ive already figured out how to fix this, unfortunately I dont know how to write the sql statement. I need a way to do this;

SELECT TOP 100 * FROM items WHERE
id != $varid
CASE DATEDIFF(day, $vardate, date) WHEN 0 THEN AND description >= $vardesc
AND DATEDIFF(day, $vardate, date) >= 0

Can anyone help? Any other suggestions?
Thanks in advance.

 

[wyrd]

Deleted post, edit..

Nevermind this post. Silly me. I guess the logic part of my brain decided to break down today.

Anyway its as simple as this (duh to me)...

WHERE (date > $date OR (datediff = 0 AND description >= $desc))

 

[jfackler]

Originally posted by wyrd

Silly me. I guess the logic part of my brain decided to break down today.

[/B]

Wyrd,
Elegant solution. Thanks for posting it back. Folks rarely do so and its unfortunate. All of us benefit from these solutions. Contrary to your quote, you continually impress me with your way of approaching a problem logically. As is always the case, explaining the problem to someone else frequently clarifys the problem. Gives you a chance to wrap your brain around it and view it from a different perspective. Its the great thing about this forum...not so much that others solve our dilema but that they point us in the right direction or pull us away and show us an approach from a different direction.

You can not bend the spoon, that is impossible. The trick is to realize, there is no spoon.


Jon

 

[wyrd]

Contrary to your quote, you continually impress me with your way of approaching a problem logically.

You shouldnt be. I posted this question in several places, and in one someone gave me the above as a possible solution (which worked, obviously).

And it is true, silly me. In a regular programming language, something like this would of been a no brainer for me. Unfortunately SQL programming seems to be taking a different toll on my brain. Perhaps its because Im still fairly new at SQL programming and Im not used to the logic behind programming it quite yet (much like I was when I first started programming in general)

------------

Anyway, back to my post.

If anyone is curious about efficiency here, I (think) that Ive found yet another possible slow down. Aparently the DATEDIFF() function checks each row individually, and I should be using a typical expression like so; date = $vardate. This would allow it to take advantage of the indexes in place, so it should execute much faster.

More on DATEDIFF(). It becomes slower as the dates become larger (as my queries are ordered by date)

Example:
DATEDIFF(day, begindate, enddate) <= 0 would match all end dates that are less than the beginning date. Since sorted by date, this would work fairly quickly if the begindate was fairly low, something in 1997. However if it was 2004, it would work a little bit slower as it has to keep comparing every single date up until 2004.

Since Im not looking for specific dates (which is what DATEDIFF() should be used for), I should be using begindate >= enddate, that way it can do ranges and checks based off the index keys, rather then doing a DATEDIFF() on each single row.

Whether or not this is 100% accurate Im not sure, but from the test qeuries Ive ran between the two, it seems like this is where some slow down is being caused.