September 11, 2009 at 8:21 am
I have table Locations
Location varchar(5)
Filled bit
Here is the data in Locations:-
A1 0
A2 0
A3 0
A4 0
A5 0
A6 0
A7 0
A8 0
A9 0
A10 0
A11 0
A12 0
now my problem is that when a user gives me name of any location and no of locations to be added
i need to provide him the name of other locations by counting no of locations he wants. for example , a user tells me that i need A1 and next 3 locations,
so i need to find next three locations after A1 which are A2,A3 and A4 in this case. And i have no idea how to that! so please can anyone help me here
September 11, 2009 at 1:13 pm
Select top 4 location
from Locations
where location >= 'A1'
September 11, 2009 at 1:16 pm
Martin Davies (9/11/2009)
Select top 4 locationfrom Locations
where location >= 'A2'
"ORDER BY location" should be added to this query.
Otherwise results may be unexpected.
September 14, 2009 at 12:48 pm
Then what happens when you try location >= 'A9' ?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 14, 2009 at 2:33 pm
Greg Snidow (9/14/2009)
Then what happens when you try location >= 'A9' ?
Well, you're right, the ORDER BY Clause will lead to wrong results 🙁
Nevertheless, I'd "blame" it on the data concept and not on the SQL statement (I'm sorry, schauhan13).
We don't know what the original table structure is: is it a heap or an indexed table?
If the latter: what columns are part of the index?
If the former: can it be guaranteed that the heap will always stay a heap and never ever anybody comes across and add an index to the Location column? If this happens, all of a sudden the very same query will result in different data. The sam is going to happen if there is an update of some values of the location column or a "misordered" insert.
What I'd do is to add a computed column to get the numeric part of the location and query against this column.
September 14, 2009 at 2:40 pm
If your system of ordering the records has to be in tht format you could try something along these lines (Not tested):
where right( location , len( location ) - 1 ) >= right( anylocation , len( anylocation ) - 1 )
This assuming that the structure of your location code is alway a single letter followed by only digits.
N.B. This is just what you COULD do. It's not a substitute for a more appropriate method of ordering the records.
September 14, 2009 at 2:41 pm
would adding an identity column help?
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
September 14, 2009 at 2:59 pm
XingThing (9/14/2009)
would adding an identity column help?
From my personal point of view and with respect to the original question: no.
Even with an identity column a mixed insert or an update of existing values may cause changed results.
September 15, 2009 at 9:05 am
shauhan13, does the letter portion of the location have any significance? If so, you could add a column to the location table to hold only the letters, which would make it easier to lookup values. Is the number of letters preceeding the numbers *always* 1? If so, this should work.
SELECT TOP 4
Location
FROM #locs
WHERE CONVERT(INT,SUBSTRING(Location,2,LEN(Location)-1))> = 9
AND SUBSTRING(Location,1,1) = 'A'
ORDER BY CONVERT(INT,SUBSTRING(Location,2,LEN(Location)-1))
What if, at some point in time, it becomes necessary to add two letters to the beginning of a location? When that happens, the above will fail. You can try the below, but it will only work for up to two letters. For three or more, you will have to add another case. And, you should note you can combine derived tables t1 and t2, but I find it easier to visualize if I break out the actions. Also, I made the assumption that the physical order of the rows is not consistent with the desired sequence of locations. Anyhow, I hope this helps.
First the unordered test data
IF OBJECT_ID('TempDB..#locs','u') IS NOT NULL
DROP TABLE #locs
CREATE TABLE #locs
(
Location VARCHAR(10)
)
INSERT INTO #locs
SELECT 'A9' UNION ALL
SELECT 'AA9' UNION ALL
SELECT 'A10' UNION ALL
SELECT 'AA10' UNION ALL
SELECT 'AA11' UNION ALL
SELECT 'A11' UNION ALL
SELECT 'B12' UNION ALL
SELECT 'A12' UNION ALL
SELECT 'B9' UNION ALL
SELECT 'B10' UNION ALL
SELECT 'B11' UNION ALL
SELECT 'AA12'
And now the query
SELECT TOP 4
location
FROM
(--T2 breaks out the numbers
SELECT
Location,
Letters,
Numbers = CONVERT(INT,SUBSTRING(Location,LEN(Letters) + 1,LEN(Location)-LEN(Letters)))
FROM
(--T1 breaks out the letters
SELECT
Location,
Letters = CASE WHEN ISNUMERIC(SUBSTRING(Location,2,1)) = 1 THEN SUBSTRING(Location,1,1)
WHEN ISNUMERIC(SUBSTRING(Location,2,1)) = 0 THEN SUBSTRING(Location,1,2)
END
FROM #locs
) t1
) t2
WHERE LEFT(Location,2) = 'AA'
ORDER BY Letters,Numbers
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply