SQL - SELECT DISTINCT myfield

Tim Rymer

Hey, is there any way when you select a distinct field, to get the rest of the fields in the table? Something like this is what i'm "wanting it to do": aka: SELECT * DISTINCT myfield FROM MyTable WHERE isFunky = True so is there any work around? i mean anything that you can do in vb6/access 2000? I dont know much about sql, just the basics, so if i can do anything obvious let me know, cuz truth is i probably dont know how to do it or wouldnt think of it. ~Timothy T. Rymer http://tim.xpertz.com http://www.digipen.edu http://www.ttrx.com

Mazdak

I can't understand your question,its not clear but I know the statement you write is correct and work. Mazy **"If I go crazy then will you still Call me Superman If I’m alive and well, will you be There holding my hand I’ll keep you by my side with My superhuman might Kryptonite"**Kryptonite-3 Doors Down

Tim Rymer

Ok, everyone always sees tutorials on how to use "SELECT DISTINCT field" and then they name their field on which they want each record to have a different value in that field. So when you use just that statement, all you get is: CUSTOMER_NAME ----------------- Jimmy Steve Guido Barry Cloud Kefka Jonny ----------------- I'm wondering how i can setup my Sql statement so that i can get the rest of that information with it, in vb6/access 2000. "SELECT * DISTINCT url FROM Emails WHERE checked = False ORDER BY foundwith" Passing this into a data object doesnt work, i want to be able get this in a recordset: CUSTOMER_NAME | ID | CHECKED | FOUNDWITH | URL ------------------------------------------------ Jimmy | 45 | False | 4 | "http://www.web.com" Cloud | 46 | False | 1 | "http://www.howdy.com" Kefka | 47 | False | 1 | "http://www.studmuffin.com" ------------------------------------------------ So as long as everyone has been "CHECKED" from that url, then i dont want to look at that "URL". Hope that explains my problem. Read my example above, does that look like it should work to you? i get RunTimeError 3075: Missing operator in query expression (* DISTINCT url) any reason why? thanks in advance. ~Timothy T. Rymer http://tim.xpertz.com http://www.digipen.edu http://www.ttrx.com

DanielO

hi, you could try the following SELECT DISTINCT CUSTOMER_NAME ID CHECKED FOUNDWITH FROM Emails WHERE checked = False ORDER BY foundwith for all fields except url or SELECT DISTINCT URL FROM Emails WHERE checked = False ORDER BY foundwith for url field only you are right about "Select * Distinct .." won't work Daniel O

Mazdak

Yes,* DISTINCT url is wrong.You can't use anything before DISTINCT expression.After DISTINCT you listed the column you want to be distincted and only they will be return. So: If you write:

SELECT DISTINCT * from Emails where checked=False

or

SELECT * from Emails where cheched=False

then only you will see same result .If you write:

SELECT DISTINCT url from Emails where checked=false

Then you will see only url column. And this statement:

SELECT DISTINCT url,ID from Emails where CHECKED=false

Return url and ID column and their COMBINATION will be unique. If you write:

SELECT * FROM Emails where Checked=false

you will get all columns that their Checked is false.You can't have DISTINCT expression here because if you have two rows with the same URL then which one should return?Maybe both of them have false record for CHECKED column.So as I understand the best statement for you is the last one.Why do you want DISTINCT here? Hope that helps.If you need more help or information you are welcome. :) Mazy **"If I go crazy then will you still Call me Superman If I’m alive and well, will you be There holding my hand I’ll keep you by my side with My superhuman might Kryptonite"**Kryptonite-3 Doors Down

Anonymous

I vote for this answer. But i think you have to seperate field names with commas.

Tim Rymer

SELECT DISTINCT CustomerName, ID, Checked, FoundWith, URL So you're saying that I will get every Record with a distinct URL with this statement? If so will the fact that it has checked(which may all be false at the beginning), or foundwith(which will more than likely have other urls to have the same FoundWith = # with them) so if i state all of those as having to be DISTINCT, does that mean it checks all before adding them? Because basically if there's a statement that says this: SELECT DISTINCT URL, Checked FROM Emails WHERE Checked = False Isnt that going to cause problems? because say they're all false, does that mean i'm to get only 1 urls back no matter what the difference in URLs? And this statement, DanielO wrote: you could try the following SELECT DISTINCT CUSTOMER_NAME ID CHECKED FOUNDWITH FROM Emails WHERE checked = False ORDER BY foundwith for all fields except url I'm just trying to get one url that isnt checked, that's all. The only reason why i'm doing that is so that I dont have to open up another recordset and look for all Customers from that URL, then see if they've been checked or not, and if they've all been checked, then goto the next url. This is all really just fickle, I just wanted to know if there was a way to get all the fields from a Record, where i had a DISTINCT Field that had to be different, then if i have that first distinct record, i could see if it had been checked, and if not then move onto the next one. *shrug* Thanks guys. ~Timothy T. Rymer http://tim.xpertz.com http://www.digipen.edu http://www.ttrx.com

notadood

If I am reading this thread correctly than you should be able to use the GROUP BY clause. It does the same thing as DISTINCT but uses a different format. It would look something like: SELECT CUSTOMER_NAME, ID, CHECKED, FOUNDWITH, URL FROM Emails WHERE CHECKED= False GROUP BY CUSTOMER_NAME, ID, CHECKED, FOUNDWITH, URL ORDER BY FOUNDWITH