| T O P I C R E V I E W |
| Carefree |
Posted - 29 April 2012 : 15:50:17
I have three issues.
- Select distinct values for one field if ALL values for another field are identical - like this:
SELECT DISTINCT A, B FROM TABLE WHERE (ALL B=1)
- Identify whether values are repeated in a column, if certain other conditions are also met - like this:
SELECT DISTINCT A, B FROM TABLE WHERE (C>'' AND B='') GROUP BY B HAVING COUNT(*) > 1
- Identify distinct values when values in another column are repeated - like this:
SELECT DISTINCT A, B FROM TABLE GROUP BY (B HAVING COUNT(*) > 1) ORDER BY A ASC
Whenever I use "GROUP BY", w3wp.exe freezes until it times out. Anyone have a clue why? |
| 15 L A T E S T R E P L I E S (Newest First) |
| Carefree |
Posted - 13 May 2012 : 11:47:09
That solved it! Thanks you two. |
| ruirib |
Posted - 12 May 2012 : 06:07:25
quote:
Originally posted by HuwR
Your HAVING clause should come after the GROUP BY not BEFORE
so the sub query would be (SELECT INCIDENT_GT FROM INCIDENTS GROUP BY INCIDENT_GT HAVING COUNT(*)>1)
That was my mistake, I typed the SQL without even testing it.
It does work when the syntax error is corrected (I have also corrected it in my original reply). |
| HuwR |
Posted - 12 May 2012 : 02:13:40
Your HAVING clause should come after the GROUP BY not BEFORE
so the sub query would be (SELECT INCIDENT_GT FROM INCIDENTS GROUP BY INCIDENT_GT HAVING COUNT(*)>1) |
| Carefree |
Posted - 11 May 2012 : 02:37:29
Sorry for long pauses, they're keeping me very busy. The cognitive people say the rate of deterioration has slowed, so maybe this helps.
Anyway, sure. Sample DB
Example of 3: SELECT DISTINCT INCIDENT_SU FROM INCIDENTS WHERE INCIDENT_GT IN (SELECT INCIDENT_GT FROM INCIDENTS HAVING COUNT(*)>1 GROUP BY INCIDENT_GT) |
| ruirib |
Posted - 06 May 2012 : 17:23:05
Maybe if you give an example table? |
| Carefree |
Posted - 06 May 2012 : 16:59:51
Sorry for my delay in replying, I've been trying to make it work (without success). |
| ruirib |
Posted - 02 May 2012 : 18:19:49
The subquery would be something like (SELECT B FROM TABLE GROUP BY B HAVING COUNT(*)>1) and the overall query could be:
SELECT DISTINCT A FROM TABLE WHERE B IN (SELECT B FROM TABLE GROUP BY B HAVING COUNT(*)>1 )
I did this without testing, so it may actually need a bit of testing to make sure it would work as you'd want. |
| Carefree |
Posted - 02 May 2012 : 16:10:10
Yes, it does. Have an example of how to handle 3? |
| ruirib |
Posted - 02 May 2012 : 10:32:01
Does your table have a primary key? If it does, 1. and 3. can be solved using IN and a subquery. |
| Carefree |
Posted - 02 May 2012 : 10:05:55
No, didn't work. Same result. Thanks for trying. |
| HuwR |
Posted - 02 May 2012 : 09:29:56
You wouldn't normally use a DISTINCT clause that way, if you are using groupby since grouping by stuff is very similar to requesting distinct anyway
instead try doing something like
SELECT A, B FROM TABLE WHERE (C>'' AND B='') GROUP BY B HAVING COUNT(DISTINCT A) > 1
|
| Carefree |
Posted - 02 May 2012 : 09:05:25
They just freeze until they timeout. I think using ALL in #1 tells it that I'm retarded but #2 & #3 are due to using "distinct AND group by" in the same query, but I haven't found anything saying that I shouldn't. |
| HuwR |
Posted - 02 May 2012 : 06:27:30
when you execute them, do they error or just not return what you were expecting? |
| Carefree |
Posted - 02 May 2012 : 02:29:44
Yes |
| HuwR |
Posted - 02 May 2012 : 02:00:57
are those exactly as you have executed them ? |
