Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unique records using replace -> group by not working 1

Status
Not open for further replies.

1DMF

Programmer
Jan 18, 2005
8,795
GB
Hello,

I'm having problems using the replace and then being able to group by the results.

E.G.
Code:
SELECT REPLACE(REPLACE(version,' (Global)',''),' (Local)','') AS myversion, COUNT(1) AS Cnt FROM grank WHERE ((position > 0 AND position <= 100) AND refdate BETWEEN '2009/10/27' AND '2009/11/02') GROUP BY version ORDER BY Min(id) ASC

This does not produce unique records based on myversion created with the AS clause.

If I change the Order By to include myversion, it works in mySQL but errors on MS SQL saying invalid column name?

How do I replace some content from a column and group to produce unique records?

Cheers,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
Code:
SELECT REPLACE(REPLACE(version,' (Global)',''),' (Local)','') AS myversion,
       COUNT(1) AS Cnt
FROM grank 
WHERE ((position > 0 AND position <= 100) AND 
       refdate BETWEEN '20091027' AND '20091102') 
GROUP BY REPLACE(REPLACE(version,' (Global)',''),' (Local)','')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
lol - thanks, i actually realised that might be the case and it works - d'oh.

I must say mySQL seems far more easier to work wit, why on earth would you want to have to type all that again in the group by clause?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
why on earth would you want to have to type all that again in the group by clause?

you wouldn't have to if your database engine supported the SQL-99 standard, which in this instance apparently SQL Server does not

:)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Thanks r937 for clearing that up.

It didn't make sense not being able to group by the variable created on the fly with the AS clause.

Makes coding stuff cross platform a pain in the butt, hey ho, it's all working now.

:)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
Alternative solution would be

select count(*) as Cnt, myGroupField from (select myComplexFunction as myGroupField from myTable) X group by myGroupField
 
what's the overhead of creating an 'on the fly' temp table in this manner?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
Hard to say - I didn't test. If your table in big and you can run both queries and then post the execution times, it would be appreciated.
 
The table is tiny, so I don't think I could perform a comparison.

I'll wait till it grows and then see what happens, but it might be a while, I don't get that many visits to this site from google :-(

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
you wouldn't have to if your database engine supported the SQL-99 standard, which in this instance apparently SQL Server does not

Standard sql only allows for column references in a group by clause. You can neither use expressions nor correlation names.
 
thanks swampboogie

i guess Celko erred then, on page 435 of SQL For Smarties (3rd ed) when he said:
SQL-99 allows queries that are grouped on the result of a computed column. For example, to do a report by months on sales, you would write:

SELECT EXTRACT(MONTH FROM sales_date) AS rpt_month,
SUM(sales_amt)
FROM Sales
GROUP BY rpt_month;

This is a departure from the SQL processing model in previous standards, which allowed only column names.
Since Celko helped write the standard, i figured he would probably know what the standard allows, but i'll shoot him an email to let him know that he was wrong

:p



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
:) Make the site better - you would have more hits.
You don't have to listen to my music if you don't like it, it's not like it cost you anything :p

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
r937, let me know when you have clarified who's right.

I like the idea Celko is suggesting very much, but would appreciate confirmation if this is the case or not.

All I know is it works on mySQL and not MS SQL!

Is that because mySQL is more standards compliant or less because it's allowing something that isn't in the standard?

Is this another case of MS and standards, or a case of their being different standards that no-one can agree upon.

Even W3C is just seen as a bunch of anally retentive geeks in a basement by many SEO experts and web coders, it's taken MS until IE8 to have a fairly decent standards compliant browser.

Do the SQL standards suffer from the same problems?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> PERL v1.5 beta (FusionCharts)
 
In Visual FoxPro you can use Numbers here, e.g.

select count(SomeField) as CntField, SomeComplexExpression as GroupField from myTable group by 2 order by 1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top