Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 17th, 2005, 03:54 PM
Authorized User
 
Join Date: Mar 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tricky Query

I have a table, Table1, with 4 columns - SvcCode,Type,From, and Description. The "From" column contians values, both numeric an alpha numeric. I need to be able to find where a specified value fits in the "From" column. The value returned should be the floor value in the range.

For instance, lets assume there are three values in the "From" column, 00100,00200, and 00300. If the user entered 00110 we would want it to return 00100.

Any ideas on how to write the query to accomplish this?
 
Old May 18th, 2005, 05:03 AM
Authorized User
 
Join Date: May 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 i guess you should check the third digit of the entered data with the data in your from table and with the one it matches return that to the user.you can get the third digit by using substring.


 
Old May 18th, 2005, 08:26 AM
Authorized User
 
Join Date: Mar 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That would work in some situations but what about the scenario's where the "From" column contains values like 00110, 00120, 00130,00210,00220,00230, etc and the value entered is 00115? Or what bout when the enterd value is alphanumeric? Ideas?
 
Old July 26th, 2005, 09:47 AM
Authorized User
 
Join Date: Jun 2004
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

maybe the FIRST Function can help

SELECT FIRST(Table1.FROM)
FROM Table1
WHERE Table1.FROM <= YOURINPUT
GROUP BY ...
ORDER BY ...

"Life is a Database"
 
Old August 31st, 2005, 08:59 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

there is also a Max(columnname) MySQL function built in, may be of use???

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt
 
Old August 31st, 2005, 08:59 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

http://www.mysqlfreaks.com/statements/27.php

more info.......

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt





Similar Threads
Thread Thread Starter Forum Replies Last Post
Tricky Queries balesh.mind SQL Server 2005 5 April 12th, 2008 10:29 PM
A tricky question mike_remember ASP.NET 1.0 and 1.1 Professional 3 October 27th, 2006 08:17 AM
Tricky logic (for me) cedwards Dreamweaver (all versions) 5 April 5th, 2006 04:47 PM
Alright, here's a tricky one... Anubis Access VBA 14 November 19th, 2003 04:22 PM
Very tricky challenge 12th_Man SQL Language 3 June 20th, 2003 06:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.