Syntax for queries based on ranges

Syntax for queries based on ranges

Post by Andrew Lietz » Tue, 28 Sep 2004 23:19:51



I want to create a SELECT statement to retrieve records within a
range.   Here is what I have so far:
For all records that start with "A" in field HH_ID.

SELECT * FROM `Z_HH_Prototype` WHERE `HH_ID` like '%A_____%'  -- This
returns all records that begin with "A", followed by 5 other characters
no matter what they are (91 records in this case).  I'd like to be able
to use something like BETWEEN, but it appears that I don't understand
how this comparison operator works.

SELECT * FROM `Z_HH_Prototype` WHERE `HH_ID` BETWEEN '%A_____%' and
'%B_____%' returns 0 rows, as does this statement

SELECT * FROM `Z_HH_Prototype` WHERE `HH_ID` > '%A_____%' and <
'%B_____%' (yields a SQL syntax error) as does SELECT * FROM
`Z_HH_Prototype` WHERE `HH_ID` > 'A_____' AND < 'B_____'

Obviously, I don't understand how to retrieve a range of records.  
Help!!! Incidentally, I know that the field HH_ID only has six characters.

Andrew L.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

 
 
 

1. Unique Constraint Based on Date Range

I'm looking to apply a unique constraint to a table such that field A
must be unique based on the date range from Field B to Field C.

This is for a rate based service model whereby, for example, $5 is worth
1 hour of Internet access.   But next week, because of increased
competition, $5 is worth 2 hours of Internet access.  I want to maintain
a history what $5 bought during a specific period of time.

create table rates (
    effective_date AS timestamp,
    expiry_date AS timestamp,
    cost AS numeric (12,2),
     access_time AS integer  (in minutes)
);

So for a given cost, there may not be a record where the effective date
of one record overlaps the expiry date of another (and vice versa).

Example record set (effective date, expiry date, cost, access_time):

2003-01-01 | 2003-01-15 | 5.00 | 60
2003-01-15 |  infinity | 5.00 | 120
2003-01-01 | infinity | 1.00 | 10

An attempt to insert another 5.00 rate effective now would fail, because
a 5.00 rate exists that doesn't expire (i.e. the expiry date would have
to be updated to the effective date of the new record minus 1 second).

I can enforce this from the front end, but a db constraint would be great.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

      message can get through to the mailing list cleanly

2. DRDOS and NDO98

3. query based on result of another query

4. UPSs and HP-UX

5. query syntax in new mysql query browser

6. Looks Bad for DEC Suit

7. query date ranges

8. what's next? (56 or ISDN)

9. Help with Date Range Query

10. strange behavior with query optimizer regarding 'range'

11. query date ranges - step 2

12. Repost [Fwd: Query Range]

13. Dirt Slow Query On Datetime Range