i'll start it off

say i have multiple rows of data and each have the following numbers in a column:

0.125
0.050
-0.025
-0.125


how do i find the one closest to zero? if 2 are just as close to zero, take the positive one...

limit 1 closest to zero ... if a positive and negative are just as close to zero, take the positive...

7/8/2007 2:36:03 PM

min(abs(col_name)) order desc by col_name limit 1

7/8/2007 3:21:15 PM

nice thread, i'll be posting in it soon

7/8/2007 3:40:16 PM

me too

7/8/2007 3:50:16 PM

Quote :

"min(abs(col_name)) order desc by col_name limit 1"

select DISTINCT a,b,min(abs(c)) from table where 91 < this and 75 > that group by a order by c desc;


that worked and actually let me select the one closest to zero for each 'a' (group by did that).


PROBLEM:

by selecting the absolute value of c ... it returns that so i don't know which results are positive or negative...

PROBLEM 2:

looks like it's actually still selecting the most negative 'c' and not comparing which is closest to zero absolutely

[Edited on July 8, 2007 at 8:02 PM. Reason : ]

7/8/2007 7:36:40 PM

absolute isn't actually working... if i remove absolute it returns the same results just w/o the absolute value returned....

so it's not actually comparing which is closest to zero absolutely




what if i wanted to find results closest to the number 1?


[Edited on July 8, 2007 at 8:16 PM. Reason : ]

7/8/2007 8:06:38 PM

Giving us actually SQL queries that don't involve "this" and "that", along with actual ideas of the data you're using is a good way to actually get help.

7/9/2007 12:25:20 AM

Given a table with those 4 values you originally posted, this (similar to what Lowjack posted) worked for me in MySQL.

SELECT MIN(ABS(num)), num FROM test GROUP BY ABS(num) ORDER BY num ASC LIMIT 1;

I added the second field so you get the actual value and grouped on ABS(num).

As for how to get values close to 1, that is tricky. I will get back to you... probably after someone else does

7/9/2007 12:32:36 AM

^yes, that's the idea. Not sure what the group by is for there.

Quote :

"select DISTINCT a,b,min(abs(c)) from table where 91 < this and 75 > that group by a order by c desc; that worked and actually let me select the one closest to zero for each 'a' (group by did that). "

No one knows wtf this means.

Quote :

" PROBLEM: by selecting the absolute value of c ... it returns that so i don't know which results are positive or negative... "

That's why you sort by c (as dirtymonkey shows, you might have to include that in the selected fields to sort by it).

Quote :

"PROBLEM 2: looks like it's actually still selecting the most negative 'c' and not comparing which is closest to zero absolutely "

That's weird. You can also just square c instead of absolute value.

7/9/2007 12:47:38 AM

^ the group is there only because it wouldn't let me select the abs and the original value without it - it threw an error.

7/9/2007 12:52:55 AM

I see. I will try it out at work tomorrow. Does squaring eliminate the need to do the group by?

7/9/2007 1:10:23 AM

i'll solve this shit for you in the morning if it's still not fixed.. too tired right now

7/9/2007 2:52:55 AM

Quote :

"As for how to get values close to 1, that is tricky. I will get back to you... probably after someone else does "

SELECT top 1 ABS(num - 1)  as diffNum, num FROM test ORDER BY diffNum ASC ;

7/9/2007 11:46:55 AM

while trying to figure out the "close to 1" problem I found an error in my previous query that i didn't notice until adding more test rows. this one is simpler and doesn't require any grouping. plus, it works

SELECT ABS(num), num FROM test ORDER BY ABS(num) ASC LIMIT 1;

7/9/2007 6:48:15 PM

the close to 1 was easy to do. since you know how to find the closest to 0. You subtract one from each and you can do basically the same query.

from this you can find the closest to x, by adding the inverse of the number you want to find.

depending on what you want to accomplish/the end goal and the database you are using there might be easier ways to do these things. but for a query this will work.

any other ones?

7/9/2007 8:18:25 PM

What's the proper way to run a query by dates? Like I want all entries before 1/1/1972... But I get a data mismatch error.

7/12/2007 3:01:38 PM

are you formatting it correctly? YYYY-MM-DD HH:MM:SS for datetimes, YYYY-MM-DD for dates

[Edited on July 12, 2007 at 3:16 PM. Reason : ie use 1972-01-01 not 1/1/1972]

7/12/2007 3:15:51 PM

I think it has the damn dates stored as a string.

7/12/2007 3:17:17 PM

d'oh

7/12/2007 3:33:44 PM

Yeah. All the date fields are stored as a string. I don't have write access. Only read to run queries for data mining. WTF do I so. I need to stratify the data into date ranges.

7/12/2007 3:52:16 PM

what's the date format?

like is it a string like '12/4/03' or a string like 'December 4th, 2003'?

7/12/2007 4:13:52 PM

stored like 12/23/1986

7/12/2007 4:30:25 PM

what db are you using...

something like this could work

SELECT * FROM DataBaseTable WHERE CONVERT(DATETIME, theDateColumnThatIsStoredAsString) > '19720101'

7/12/2007 5:26:51 PM

Migrate the database to modernity.

7/12/2007 10:28:18 PM

Ok.. decided not to deal with this shit in MS Access. How do I export 137k rows of data into Excel? Excel sheets have 65k limits, trying to selectively extract 60k (in batches) crashes Access.

7/16/2007 1:05:03 PM

mysql.com

7/16/2007 1:09:16 PM

you could export it to a tab-delimited file and use a text editor to copy/paste your rows to excel

thought whats the point in involving excel if it can't handle all your rows in one sheet anyways

7/16/2007 1:09:48 PM

are you trying to export the whole db (assuming there is more than the dates)? if so just create a unique key (if it doesn't exist) and export those two columns if they can fit into excel. then sort in excel... figure out which you need and then you have those keys to link back into access.

do you have access to visdata?

7/16/2007 5:06:29 PM

Update to Office 2007.

It lets you have something like 6 million rows and 65k columns.

7/16/2007 6:04:00 PM

Quote :

"SELECT ABS(num), num FROM test ORDER BY ABS(num) ASC LIMIT 1;"

thanks that worked great!

7/16/2007 9:56:03 PM

no... just query results...137k, entire database is much much bigger

7/16/2007 10:10:52 PM

Okay.. i got TOAD installed.

It connects to the database but it cannot find the tables. Do I need to setup a ODBC driver for TOAD?

7/23/2007 1:06:32 PM

Does anyone here run MySQL Cluster?

7/23/2007 11:16:53 PM

Is this right? It's a subselect.... Basically the first statement returns around 26k entries. Second statement returns 13k entries that overlaps with the 26k from first. I want a list of unique entries that is the difference between the two statements.

Quote :

" SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC')) AND B.box_id NOT IN (SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B, RIMS.FOLDERS f WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC')) AND f.box_id = B.box_id); "

7/27/2007 1:39:35 PM

If I understand what you are asking for then this should do it...

SELECT B.BOX_ID FROM RIMS.BOXES B LEFT JOIN RIMS.FOLDERS f ON B.box_id=f.box_id WHERE (B.BOX_LOCATOR IS NULL) AND (B.BOXLOC='WESTPTRC');

However, were you asking for unique entries in B only or unique entries in both B and f?

7/27/2007 2:26:53 PM

unique entries in B.. let me try that, thanks

Okay... tried it, did not return what I wanted...

Quote :

"SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B LEFT JOIN RIMS.FOLDERS f ON B.box_id=f.box_id WHERE (B.BOX_LOCATOR IS NULL) AND (B.BOXLOC='WESTPTRC');"

I added DISTINCT to the code you suggested to get unique results.
It returns the samething as

Quote :

" SELECT B.BOX_ID FROM RIMS_BOXES B WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC'));"

[Edited on July 27, 2007 at 2:43 PM. Reason : edit]

7/27/2007 2:38:29 PM

Quote :

"SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC')) AND B.box_id NOT IN (SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B, RIMS.FOLDERS f WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC')) AND f.box_id = B.box_id); "

That's what I wrote...

First part (before subselect) has less conditions... it doesn't have:

Quote :

"AND f.box_id = B.box_id"

So the results returned were around 26k. The second part (sub-select) is:

Quote :

"(SELECT DISTINCT B.BOX_ID FROM RIMS.BOXES B, RIMS.FOLDERS f WHERE (((B.BOX_LOCATOR) Is Null) AND ((B.BOXLOC)='WESTPTRC')) AND f.box_id = B.box_id);"

That has the added condition of:

Quote :

"AND f.box_id = B.box_id"

Which looks up the B.box_id against f.box_id and only returns the matches. So it cuts the 26k from the first part down to around 13k.

With:

Quote :

"AND B.box_id NOT IN "

I want to find the entries of [Part1] - [Part2].

And I am having trouble getting the query to run... (yes the columns are indexed).

7/27/2007 2:50:00 PM

Is BOX_ID not a unique key in BOXES? If it is then you shouldn't need DISTINCT. DISTINCT is greatly overused.

SELECT DISTINCT B.BOX_ID
FROM RIMS.BOXES B LEFT JOIN RIMS.FOLDERS f ON B.BOX_ID=f.BOX_ID
WHERE (f.BOX_ID IS NULL) AND (B.BOXLOC='WESTPTRC') AND (B.BOX_LOCATOR IS NULL)

Sorry, forgot the f NULL.

7/27/2007 2:53:30 PM

Okay... that worked.... can you explain why my code didn't work (beside the fact that I suck @ SQL).

7/27/2007 2:57:03 PM

When you do SELECT FROM A WHERE A.ID=asdasd AND NOT IN (SELECT FROM B WHERE B.ID=fdgdfg)
the SELECT FROM B is done first. In your case, the inner SELECT involving FOLDERS is done first, then the outer SELECT is performed. It is possible to get a result in the manner you have constructed it but the LEFT JOIN is much more efficient. It is creating a join based upon the box_id's in common that is weighted to the "left" (BOXES). This means it returns all items in B and f that match plus all items in B that fit the query but don't have an f matching pair. By adding the WHERE f.box_id IS NULL requirement you eliminate all the portions of the query that have both B and f so you only get records that fit the query but are unique to B. Look at tyhe LEFT JOIN example: on this page
http://www.w3schools.com/sql/sql_join.asp
If you added "Orders.Employee_ID IS NULL" you would only return the 2 records without a Product (Svendson, Tove and Pettersen, Kari)

7/27/2007 3:05:05 PM

FYI, I learned how to do this from a guy on http://www.dbforums.com/
I was trying to write synch routines for a distributed database (laptops workng in the field with a connection that would later be plugged in). I had to find records that were on the remote DB that were not on the central server DB and vice versa for inserting and deleting. I just happen to have the same problem you did in the past. I HIGHLY recommend you get legit answers from http://www.dbforums.com/forumdisplay.php?f=11 next time you need help with SQL statements. The guys name was r937. He's a professional SQL consultant.

7/27/2007 3:09:18 PM

TABLE 1 has columns: name1, date, score

TABLE 2 has columns: name1, date, name2

i need a query that gives me a table with rows of: name1, date, name2, and score

i assume that i need a join but i suck at sql

help

----

the query

SELECT t2.name1, t2.date, t2.name2, t1.score
FROM t2 
LEFT JOIN t1 
ON t2.name1 = t1.name1 AND t2.date = t1.date;

9/21/2007 4:40:27 PM

don't you have to put FROM t1,t2 for it to use both tables? i could be wrong tho

9/21/2007 4:45:54 PM

i would certainly think so

access gives me a 'syntax error in JOIN operation' message when i try

9/21/2007 4:48:29 PM

SELECT t2.name1, t2.date, t2.name2, t1.score
FROM t1
INNER JOIN t2
ON t1.name1 = t2.name1 AND t1.date = t2.date;

Inner join is for exact matches only, left join emphasizes the left table plus the matches

9/21/2007 5:12:27 PM

that gives me a blank table

---

i guess i should mention that there are multiple entries of name1, date, and name2 in each table, but there are no duplicate rows

[Edited on September 21, 2007 at 5:19 PM. Reason : ]

9/21/2007 5:18:06 PM

t1:

name1	date	score
fred	1	55
tom	2	44
mike	3	33

t2:
name1	date	name2
fred	1	asdsd
mike	2	dfgdfg
tom	5	asd
mike	3	tgdf
fred	4	qaaaa

name1	date	name2	score
fred	1	asdsd	55
mike	3	tgdf	33

9/21/2007 5:53:11 PM

yeah that's exactly what i have and exactly what i need

and that query should do exactly that

but it isn't

fucking a

---

so i created two dummy tables and the query works, must be the data

[Edited on September 21, 2007 at 6:15 PM. Reason : ]

9/21/2007 6:00:19 PM

heres a good site for beginning-intermediate sql users: http://sqlzoo.net/

9/21/2007 6:25:04 PM

finally got it working

apparently one of the tables was built from a query that was built from another query

don't really know why that would be a problem, but i cleaned it up a bit and everything is working

9/21/2007 6:48:31 PM