10 Replies - 443 Views - Last Post: 20 June 2014 - 02:20 PM Rate Topic: -----

#1 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Compare Range values within the same Table

Posted 19 June 2014 - 09:00 AM

Hello,

I have a Mysql table like below in Mysql

1000   2000
2000   3000
3000   4000
4000   5000



I want to scale one of the column and show values between 0-10.
I tried but couldn't succeed. I want to do something like below
scaledValue = (rawValue - min) / (max - min);



Anyone knows a query to help ?

Thanks in adavance

Is This A Good Question/Topic? 0
  • +

Replies To: Compare Range values within the same Table

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9573
  • View blog
  • Posts: 36,254
  • Joined: 12-June 08

Re: Compare Range values within the same Table

Posted 19 June 2014 - 09:08 AM

That didn't make a whole mess of sense. What are you expecting for an output?
Was This Post Helpful? 0
  • +
  • -

#3 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Compare Range values within the same Table

Posted 19 June 2014 - 09:22 AM

Actually I am gonna plot these values graphically and my ranges are huge !
So to get the pixels right I must scale these values between 0-10
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9573
  • View blog
  • Posts: 36,254
  • Joined: 12-June 08

Re: Compare Range values within the same Table

Posted 19 June 2014 - 09:26 AM

Hmmm.. not doesn't quite help, but what you can do is for each column, before your query, get the min and max values.. then plug those into your SELECT for a given column name and chunk out the math.
Was This Post Helpful? 1
  • +
  • -

#5 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Compare Range values within the same Table

Posted 19 June 2014 - 09:31 AM

Yep I got it !
Thanks
Was This Post Helpful? 0
  • +
  • -

#6 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Compare Range values within the same Table

Posted 20 June 2014 - 12:05 PM

Hello,

I have a single table like below:

Name    Range1   Range2
Micheal  1000     2000
Henry     1500    2000
Augustine  2500 3000
Christina 1100   2500
Julie     5000    6000



I want to select 2 or 3 names and print them if they have intersecting ranges.
I want to know the counts which are intersecting and which re unique to each name.
I know it can be done easily with 2 tables but how can I do it within a single table ?

Thanks for the help !
Was This Post Helpful? 0
  • +
  • -

#7 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 356
  • View blog
  • Posts: 785
  • Joined: 27-June 09

Re: Compare Range values within the same Table

Posted 20 June 2014 - 12:37 PM

I don't understand your exact requirements, but if you already know how to do this with 2 tables, why don't you just write the code for that, then replace the two table names with the name of this table.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3813
  • View blog
  • Posts: 13,527
  • Joined: 12-December 12

Re: Compare Range values within the same Table

Posted 20 June 2014 - 12:42 PM

I have merged all three of your topics on the same issue. Do not start a new topic when you already have one open.
Was This Post Helpful? 0
  • +
  • -

#9 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Compare Range values within the same Table

Posted 20 June 2014 - 01:20 PM

OK thanks Andrew !

and mojo66 I tried something like this but its not working !

select t1.value from mytable t1 Inner Join mytable t2 on t1.value = t2.value where t1.value = t2.value;



Just confused a bit... new to complex queries ! would appreciate help !

Thanks
Was This Post Helpful? 0
  • +
  • -

#10 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Compare Range values within the same Table

Posted 20 June 2014 - 01:25 PM

What exactly I want is that if I select e.g, Micheal & Henry & Christina in a big table where they can have multiple value in ranges.
I want to print only intersecting ranges.

Example
Micheal  1000     2000
Henry     1500    2000
Augustine  2500 3000
Christina 1100   2500
Julie     5000    6000
Micheal  7000     8000
Henry     2500    3000
Augustine  7500 8000
Christina 2100   3500



What I want is if I select Michael and Henry I want to display only the records with intersecting ranges of values.

I hope its clear now ?

Thanks
Was This Post Helpful? 0
  • +
  • -

#11 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 356
  • View blog
  • Posts: 785
  • Joined: 27-June 09

Re: Compare Range values within the same Table

Posted 20 June 2014 - 02:20 PM

Well, to see if ranges intersect, your condition would be more like

on t1.name<>t2.name AND NOT (t1.uppervalue<t2.lowervalue OR t1.lowervalue>t2.uppervalue)


This might not produce exatly what you are looking for but play around with it and see what you can do.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1