Select Distinct Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Distinct Help

Author

Topic

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 08:48:06

Good morning sql team. After surfing the web I found this site and figured it would be a perfect fit for finding help with my present problem. I am attempting to execute a sql statement but only display items once. I have tried the select distinct and select distinctrow but without success. Can someone take a peak at the below sql statement to help identify what I am doing wrong? Thanks in advance!

strSql = "SELECT DISTINCT " & strTablePrefix & "MUSIC.M_ID"
strSql = strSql & ", " & strTablePrefix & "MUSIC.M_ARTIST"
strSql = strSql & " FROM " & strTablePrefix & "MUSIC "
strSql = strSql & " ORDER BY " & strTablePrefix & "MUSIC.M_ARTIST DESC;"

As of right now it displays all records in the database but I only want it to display one instance of all records.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-13 : 08:59:52

1. use stored procedures
2. your query will return distinct set of m_id and m_artist (distinct is applied on both columns). so it seems that you don't have duplicate values in those 2 columns.
3. what's with the strTablePrefix ???
4. maybe more info would be usefull.

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-13 : 09:05:22

Yes, what spirit1 says. But to use the data as is, try building a sql string that ends up like this:

Select min(M_ID) as M_ID, M_Artist
From Music
group by m_Artist
Order gy M_Artist desc

Be One with the Optimizer
TG

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 09:07:31

Theoretically it should return distinct values but I assure you there are duplicate values that are being displayed. The M_ID are all unique, but the ARTIST have duplicate entries (last name) The duplicates are all returned within the select box which is calling upon this sql statement. In regards to strTablePrefix this is a default value established for the forum for which this sql statement is being written. I am building a new mod for the Snitz forum and strTablePrefix (ie strTablePrefix = "FORUM_") is just part of the base code. Use stored procedures?!? At present I have an array being built after the sql statement is being made which stores the values pulled from the database:

set rsMusic = Server.CreateObject("ADODB.Recordset")
rsMusic.open strSql, my_Conn, 3
artistRows = rsMusic.getRows
rsMusic.Close
Set rsMusic = nothing

Sorry I am still a newbie with sql and everything I have learned thus far as been from trial and error and a lot of online reading...heh

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 09:23:31

TG, I am still surfing the web because when I ran your code I got the trademark of death: The page cannot be displayed This is the first time I have ever seen this particular error so I am reading up on it right now:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'M_ID' in query definition's SELECT list.

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 09:36:58

Here is what I found so far...which is why I am guesing I have never seen this error message before. Since I am using Access instead of mySQL I cannot alias my columns so Min/Max will not work for me. Any other ideas?!? Thanks in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-13 : 09:40:07

You're hitting an Access database? Not Sql Server? That explains your: "Use stored procedures?!?"

this is a Sql Server forum. There is an Access formum on the sqlteam site as well, perhaps you should post there.

Be One with the Optimizer
TG

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 09:44:53

Thanks for all your help TG. Sorry for the confusion as I just found this site and "assumed" that SQL Developer was my appropriate category....guess the "You know what they say about assuming" would most certainly apply in this case...heh Thanks again you guys. Could someone be a savior and move this post to the MS Access area so I don't double post in here?!? Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-13 : 09:44:58

>>Since I am using Access instead of mySQL I cannot alias my columns so Min/Max will not work for me.

Of course you can alias columns (and tables) in Access. My big pet peeve is when people just assume something is impossible because they receive a syntax error. Read up on it and see what syntax you need to use; a syntax error does not mean a feature is not supported, it means the syntax you are trying is incorrect.

Definitely post more specific information in the Access forum and you'll get help there.

- Jeff

TennesseeGuy
Starting Member

6 Posts

Posted - 2005-03-13 : 09:48:57

Jeff I didn't assume that I couldn't use alias in Access I just ran a search in this forum that let me to this post which stated I couldn't:

http://sqlteam.com/Forums/topic.asp?TOPIC_ID=20025

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-13 : 17:25:51

All that says is: "Access also does not allow you to alias a column with the same name as the column" It doesn't say you cannot alias a column.

BTW, I just tried it in Access 2000, and I was able to alias a column with the same name as the column. Never hurts to try it yourself.

quote:
Originally posted by TennesseeGuy

Jeff I didn't assume that I couldn't use alias in Access I just ran a search in this forum that let me to this post which stated I couldn't:

http://sqlteam.com/Forums/topic.asp?TOPIC_ID=20025

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-13 : 17:41:36

i think what Rob meant was you can't do this:

Select Max(Cost) as Cost from YourTable

but you can say:

Select Max(YourTable.Cost) as Cost From YourTable

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources