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

[Sethington]

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`

 

[r937]

might be possible... can you use a subquery in the WHERE clause?



r937.com | rudy.ca

 

[Sethington]

Yes I can. I can do anything after the FROM clause in a SQL statement. SO ORDER BY and Subqueries work too.

 

[r937]

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

 

[Sethington]

I did a little digging and I found that the GROUP BY Clause does exactually what I was looking for.

 

[SantaMufasa]

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

on HarleyQuinn back in thread759-1436397, okay?

Holiday regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[HarleyQuinn]

Seeing as he's only given one Dave, I think it's unlikely, but thank for trying. I appreciate it

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.