October 1, 2009 at 8:24 am
Does a GROUP BY on a column that is not in the select list have any bearing on the SELECT results? For example (this is in a where clause_:
AND CRD_TXT.EXPN_DTE IN (SELECT MAX (TOCTX_CARDTEXT.EXPN_DTE)
FROM TOCTX_CARDTEXT,
PNCDH_CARDHOLD,
PNCEN_CUSTENRL
WHERE TOCTX_CARDTEXT.UNQ_CRD_NBR = PNCDH_CARDHOLD.UNQ_CRD_NBR AND PNCDH_CARDHOLD.ENR_NBR = PNCEN_CUSTENRL.ENR_NBR AND TOCTX_CARDTEXT. UNQ_CRD_NBR = CRD_TXT.UNQ_CRD_NBR AND TOCTX_CARDTEXT.EFF_DTE <= CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)
AND PNCEN_CUSTENRL.CUST_NBR = 00000
-GROUP BY PNCDH_CARDHOLD.ENR_NBR)
October 1, 2009 at 8:34 am
AND CRD_TXT.EXPN_DTE IN (SELECT MAX (TOCTX_CARDTEXT.EXPN_DTE)
FROM TOCTX_CARDTEXT,
PNCDH_CARDHOLD,
PNCEN_CUSTENRL
WHERE TOCTX_CARDTEXT.UNQ_CRD_NBR = PNCDH_CARDHOLD.UNQ_CRD_NBR AND PNCDH_CARDHOLD.ENR_NBR = PNCEN_CUSTENRL.ENR_NBR AND TOCTX_CARDTEXT. UNQ_CRD_NBR = CRD_TXT.UNQ_CRD_NBR AND TOCTX_CARDTEXT.EFF_DTE <= CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)
AND PNCEN_CUSTENRL.CUST_NBR = 00000
-GROUP BY PNCDH_CARDHOLD.ENR_NBR)
If you have no aggregate functions in your select, the query will return distinct occurrences of PHCDH_CARDHOLD.ENR_NBR for records that satisfy your where conditions. Alternately, you could use the DISTINCT key word in your select.
SELECT DISTINCT PNCDH_CARDHOLD.ENR_NBR
FROM PNCDH_CARDHOLD
WHERE ...
October 1, 2009 at 8:37 am
Yes, it most certainly does.
You can't have SELECT clauses that aren't in the GROUP BY clause, but you can have GROUP BY clauses that aren't in the SELECT ones.
as an example, if your table contained:
DECLARE @Temp TABLE
(
NAME VARCHAR(20),
Income INT
)
INSERT INTO @Temp (Name, Income)
SELECT 'A', 10
UNION ALL
SELECT 'B', 20
UNION ALL
SELECT 'A', 10
UNION ALL
SELECT 'B', 20
UNION ALL
SELECT 'C', 30
UNION ALL
SELECT 'D', 40
UNION ALL
SELECT 'E', 40
UNION ALL
SELECT 'F', 40
If you said
SELECT Income
FROM @Temp
You would get:
10
20
10
20
30
40
40
40
On the other hand, if you said
SELECT Income
FROM Table
GROUP BY Income
You'll get:
10
20
30
40
Finally, if you said
SELECT Income
FROM Table
GROUP BY Income, Name
You would get:
10
20
30
40
40
40
October 1, 2009 at 8:49 am
Yes but in my case with MAX(TOCTX_CARDTEXT.EXPN_DTE) being the only value in the select statement, I dont't see how a GROUP BY on PNCDH_CARDHOLD.ENR_NBR is changing the output at all. I've attached the entire query.
October 1, 2009 at 9:26 am
First off, you should change your query to use the new standard for joins, instead of saying SELECT FROM TABLE1, TABLE2, TABLE3, etc..
SELECT MAX(TOCTX_CARDTEXT.EXPN_DTE)
FROM TOCTX_CARDTEXT
JOIN PNCDH_CARDHOLD ON TOCTX_CARDTEXT.UNQ_CRD_NBR = PNCDH_CARDHOLD.UNQ_CRD_NBR
JOIN PNCEN_CUSTENRL ONPNCDH_CARDHOLD.ENR_NBR = PNCEN_CUSTENRL.ENR_NBR
AND PNCEN_CUSTENRL.CUST_NBR = 284115
WHERETOCTX_CARDTEXT.UNQ_CRD_NBR = CRD_TXT.UNQ_CRD_NBR
AND TOCTX_CARDTEXT.EFF_DTE <= CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME)
GROUP BY PNCDH_CARDHOLD.ENR_NBR
Secondly, by doing GROUP BY, you're creating sets of records, which is something you can't do with a subquery. As an example, if this was your data set,
TOCTX_CARDTEXT.EXPN_DTE.EXPN_DTEPNCDH_CARDHOLD.ENR_NBR
'2008-01-01'1
'2009-01-01'1
'2010-01-01'1
'2008-01-01'2
'2009-01-01'1
saying SELECT MAX(TOCTX_CARDTEXT.EXPN_DTE.EXPN_DTE) would result in a recordset containing '2010-01-01'.
On the other hand, saying SELECT MAX(TOCTX_CARDTEXT.EXPN_DTE.EXPN_DTE) GROUP BY PNCDH_CARDHOLD.ENR_NBR, would result in a recordset containing two records, '2010-01-01' and '2008-01-01', since it's being grouped by PNCDH_CARDHOLD.ENR_NBR, of which there are two distinct records, and then taking the max for each set.
October 1, 2009 at 9:38 am
Thank you kramaswamy! I am rewriting to use ANSI style joins at the moment.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply