ROWNUM in MySQL

Found this surfing the other day; I’d link back but I’ve forgotten where I saw it.

SELECT @a:=@a+1 rownum, t.* FROM (SELECT @a:=0)r, (SELECT * FROM yourtable ORDER BY ordering) t

Works like a charm in 5.0.

Search for date range in Oracle

Here’s the syntax for a couple of Oracle date range queries.

SYSDATE gives you “now” which means you can search for records relative to “now.” This query selects records for the last 24 hours, from right now:

SELECT * FROM yourtable
WHERE created > SYSDATE - 1
ORDER BY created;

If you want a particular date range you can use the TO_DATE function:

SELECT * FROM yourtable
WHERE created BETWEEN
TO_DATE('02/16/2009','mm/dd/yyyy') AND
TO_DATE('03/16/2009','mm/dd/yyyy')
ORDER BY created;

This method allows you to format any date string into Oracle date format using the syntax that can be found here.

Remember — for a date without a time, the time will be returned as 12:00am. So if you need to use a “like” search, be sure to set your FROM and TO dates accordingly (from day, to day+1):

SELECT * FROM yourtable
WHERE created BETWEEN
TO_DATE( month || day || year, 'mmddyyyy' ) AND
TO_DATE( month || day+1 || year, 'mmddyyyy' )

PCI video card + onboard AGP = pain

For anyone trying to install a PCI video card on a system with only PCI slots and an onboard AGP – if you can disable your onboard video, you should be good. This can sometimes be done with a jumper on the motherboard. More likely, you will disable onboard video through BIOS. However, if presented with only two options: Auto and Onboard, you may have to unintuitively set your primary video to “Onboard.” This is because your system may become confused between the onboard video and the added PCI card, resulting in a garbled display.

Once you’ve set the video to “Onboard” your system may be able to recognize the second card as an additional monitor. After you’ve booted into Windows, in the display control panel settings, you can: activate the second display, use it as the primary monitor, and deactivate the onboard display. (Then move the monitor connector from the onboard to the PCI card output.) The drawback to this approach is that you will have a blank screen through the boot process.

(This was what I had to do to install an ATI Radeon 9250 on a Dell Optiplex GX200. Such is life when dealing with older hardware.)