| Author |
 Topic  |
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
 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? |
Edited by - Carefree on 01 May 2012 22:25:09 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 02 May 2012 : 02:29:44
|
| Yes |
 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
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
Forum Admin
United Kingdom
20584 Posts |
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
|
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 02 May 2012 : 10:05:55
|
| No, didn't work. Same result. Thanks for trying. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 02 May 2012 : 16:10:10
|
| Yes, it does. Have an example of how to handle 3? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 06 May 2012 : 16:59:51
|
| Sorry for my delay in replying, I've been trying to make it work (without success). |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
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) |
Edited by - Carefree on 11 May 2012 02:49:09 |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
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). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Carefree
Advanced Member
Philippines
4207 Posts |
Posted - 13 May 2012 : 11:47:09
|
| That solved it! Thanks you two. |
|
|
| |
Topic |
|
Select Issues
