Atten SQL query masters:

Hello, I have a table of events, the table has an id field, a date field, a name field and a priority field.
My goal is to get the next four events based on the date, however if there is an event in the table that has a high priority I would like to display that one even tho there are events that have an earlier date.
in other words if I have four unimportant events this week , and one incredibly important event a month from now. I would like to SELECT the first 3 events from this week and the incredibly important event as the 4th? How would i use an event's priority field to bump events that come before it but still remain in chronological order with the other 3 events?

thank you

I would highly recommend adding another attribute/column called PRIORITY (INT - 1/0 values).

Then you could use ORDER BY function to place the PRIORITY event at the top of your events, and then order the rest.

Example:

SELECT T.*
FROM TABLE T
ORDER BY T.Priority DESC, T.AddDate DESC
========================================
Buh Bye!

Cheers,
Me

the problem with sorting by priority first and then sorting by date is that the resulting set of records will not be ordered chronologically.
I want the results to be chronoligically ordered.
To restate problem:
TABLE (id, date, priority):
1, 01/02, not importent
2, 01/03, not importent
3, 01/04, not importent
4, 01/05, Importent

GOAL: Select ids from the next three events in chronological order, including Important events.
I want this select stmt to return ids: 1,2,4
(if you sort by priority first you get: 4,1,2)

To summarize, I want to reserve that last spot in my results to be filled with a High priority as long as one exists and is not already in the resulting set. Otherwise the last spot gets filled with the next non important event.


thanks

I think this can be done in 2 steps.
1) Select the max 3 items within this week, where it has 'not importent'
2) Select the max 1 items within 1 month, where is has 'Importent'

Then merge the results. IMPO.

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...