Select Issues

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/70200?pagenum=1
04 November 2025, 21:28

Topic

Carefree
Select Issues
29 April 2012, 15:50


I have three issues.
  1. 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)
  2. 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
  3. 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?

 

Replies ...

HuwR
02 May 2012, 02:00


are those exactly as you have executed them ?
Carefree
02 May 2012, 02:29


Yes
HuwR
02 May 2012, 06:27


when you execute them, do they error or just not return what you were expecting?
Carefree
02 May 2012, 09:05


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
02 May 2012, 09:29


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
02 May 2012, 10:05


No, didn't work. Same result. Thanks for trying.
ruirib
02 May 2012, 10:32


Does your table have a primary key? If it does, 1. and 3. can be solved using IN and a subquery.
Carefree
02 May 2012, 16:10


Yes, it does. Have an example of how to handle 3?
ruirib
02 May 2012, 18:19


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
06 May 2012, 16:59


Sorry for my delay in replying, I've been trying to make it work (without success).
ruirib
06 May 2012, 17:23


Maybe if you give an example table?
Carefree
11 May 2012, 02:37


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:
Code:
SELECT DISTINCT INCIDENT_SU FROM INCIDENTS WHERE INCIDENT_GT IN (SELECT INCIDENT_GT FROM INCIDENTS HAVING COUNT(*)>1 GROUP BY INCIDENT_GT)
HuwR
12 May 2012, 02:13


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)
ruirib
12 May 2012, 06:07


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).
Carefree
13 May 2012, 11:47


That solved it! Thanks you two.
© 2000-2021 Snitz™ Communications