Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Help: How to select distinct rows using the WHERE clause

Status
Not open for further replies.

Sethington

Technical User
May 16, 2006
34
US
I have an app at work that will only allow me to customize the WHERE clause on a SQL statement.

Currently the SQL Statement is hard coded something like this:

SELECT * FROM `ERRORS`

Is there a way to do a DISTINCT in the WHERE clause that would return the same results as:

SELECT DISTINCT `MEMBER_NUMBER` FROM `ERRORS`
 
Yes I can. I can do anything after the FROM clause in a SQL statement. SO ORDER BY and Subqueries work too.
 
okay, if a given value of MEMBER_NUMBER occurs more than once in ERRORS, you could perhaps pull out one row per MEMBER_NUMBER based on the maximum value of something else, such as ERROR_DATE

SELECT * FROM `ERRORS`
WHERE [blue]ERROR_DATE = (
SELECT MAX(ERROR_DATE) FROM ERRORS AS T2
WHERE T2.MEMBER_NUMBER = ERRORS.MEMBER_NUMBER )[/blue]

r937.com | rudy.ca
 
I did a little digging and I found that the GROUP BY Clause does exactually what I was looking for.
 
Sethington said:
I did a little digging and I found that the GROUP BY Clause does exactually what I was looking for.
Thanks, to HarleyQuinn in the Oracle 9i forum providing the shovel and elbow grease, right Seth? <grin>


Be sure to go back and plant a
star.gif
on HarleyQuinn back in thread759-1436397, okay?

Holiday regards,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Seeing as he's only given one Dave, I think it's unlikely, but thank for trying. I appreciate it [smile]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top