January 30, 2009 at 8:01 am
Hey guys, this is probably a novice question for you. I'm a GIS guy that has been tasked with creating an index for a map book we use. Now I need an index for it. I have the raw data to create this index. I can even see what needs to be done to get it, but my sql skills are very basic. Here's a sample of my data and the required output I need.
Thanks for any help
innerGridgridMaindirectionstrtNamestrtTypelfAddltAddrfAddrtAddID
A - 28S10THST061861361923778
A - 28S10THST60460660361123781
A - 28S10THST0011111723790
A - 28S10THST000023791
What I need is to combine like rows based off the 1st 5 fields matching, from there I then need to to determine my min and max value based off of the lfAdd, ltAdd, rfAdd, and rtAdd fields.
My desired output would look like this
innerGridgridMaindirectionstrtNamestrtType Minimum Maximum
A - 28S10THST 111 619
January 30, 2009 at 8:25 am
We would be happy to help you, if you can provide us with the table definitions (in form of CREATE TABLE Scripts) and sample data (in form of INSERT statements).
People tends to respond to posts quickly where they get ready-made available scripts, to know more on how to post questions to get betters answers see this article[/url]
--Ramesh
January 30, 2009 at 8:29 am
Moreover, to find the necessary indexes, its required to know what type of queries will be executed against the table.
And also, here is the code that will provide you the required output....
SELECTinnerGrid, gridMain, direction, strtName, strtType,
( CASE
WHEN lfAdd >= ltAdd AND lfAdd >= rfAdd AND lfAdd >= rtAdd THEN lfAdd
WHEN ltAdd >= lfAdd AND ltAdd >= rfAdd AND ltAdd >= rtAdd THEN ltAdd
WHEN rfAdd >= lfAdd AND rfAdd >= ltAdd AND rfAdd >= rtAdd THEN rfAdd
WHEN rtAdd >= lfAdd AND rtAdd >= ltAdd AND rtAdd >= rfAdd THEN rtAdd
ELSE NULL
END ) AS MaxValue,
( CASE
WHEN lfAdd <= ltAdd AND lfAdd <= rfAdd AND lfAdd <= rtAdd THEN lfAdd
WHEN ltAdd <= lfAdd AND ltAdd <= rfAdd AND ltAdd <= rtAdd THEN ltAdd
WHEN rfAdd <= lfAdd AND rfAdd <= ltAdd AND rfAdd <= rtAdd THEN rfAdd
WHEN rtAdd <= lfAdd AND rtAdd <= ltAdd AND rtAdd <= rfAdd THEN rtAdd
ELSE NULL
END ) AS MinValue
FROM(
SELECTinnerGrid, gridMain, direction, strtName, strtType,
MAX( lfAdd ) AS lfAdd, MAX( ltAdd ) AS ltAdd,
MAX( rfAdd ) AS rfAdd, MAX( rtAdd ) AS rtAdd
FROMSomeTable
GROUP BY innerGrid, gridMain, direction, strtName, strtType
) S
--Ramesh
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply