School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 307,099
  • Replies: 841,744
  • Topics: 140,664
  • Snippets: 4,467
  • Tutorials: 1,166
  • Total Online: 2,027
  • Members: 134
  • Guests: 1,893

Welcome to Dream.In.Code
Become an Expert!

Join 307,099 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,027 people online right now. Registration is fast and FREE... Join Now!




SELECT DISTINCT

 

SELECT DISTINCT
xheartonfire43x

1 Jul, 2009 - 10:10 AM
Post #1

D.I.C Regular
***

Joined: 22 Dec, 2008
Posts: 267



Thanked: 2 times
My Contributions
This is a SQL question where the SQL is being used through a ColdFusion Application. I want to to a SELECT DISTINCT where it pulls distinct names, but don't care about the id. Because for some reason right now my SQL seems to be looking at the id and not the name... I am not sure why.

CODE

    SELECT DISTINCT groups.name AS name,groups.id AS id FROM groups<!---,groupMembers
    WHERE (groupMembers.contact_id <> <cfqueryparam value="#URL.id#">
        AND groups.id = groupMembers.group_id)--->
    ORDER BY name


The stuff that is surrounded by <!--- and ---> is ColdFusion commented out. It is not pulling Distinct names right now. Only distinct ID's. Such as my ID is a uniqueidentifier and my name is just a regular nvarchar(50).

User is offlineProfile CardPM
+Quote Post


kmangold

RE: SELECT DISTINCT

1 Jul, 2009 - 10:19 AM
Post #2

D.I.C Head
Group Icon

Joined: 24 Jun, 2009
Posts: 166



Thanked: 7 times
Dream Kudos: 75
My Contributions
Hmm, you could group by the name if the id doesn't matter? Or don't bother querying the id.
User is offlineProfile CardPM
+Quote Post

xheartonfire43x

RE: SELECT DISTINCT

1 Jul, 2009 - 10:59 AM
Post #3

D.I.C Regular
***

Joined: 22 Dec, 2008
Posts: 267



Thanked: 2 times
My Contributions
QUOTE(kmangold @ 1 Jul, 2009 - 10:19 AM) *

Hmm, you could group by the name if the id doesn't matter? Or don't bother querying the id.


Sorry I phrased that wrong. In my webpage I build a Drop-Down list with the query where the value is the ID and the display is the Name. I know the ID will always be unique, so I just need to SELECT Distinct by name. So I do need the ID, but I know it is distinct so I don't know to worry about doing DISTINCT id's.
User is offlineProfile CardPM
+Quote Post

kmangold

RE: SELECT DISTINCT

1 Jul, 2009 - 11:10 AM
Post #4

D.I.C Head
Group Icon

Joined: 24 Jun, 2009
Posts: 166



Thanked: 7 times
Dream Kudos: 75
My Contributions
Quickly thinking, here is a possible solution: instead of using ORDER BY name, try GROUP BY name.
User is offlineProfile CardPM
+Quote Post

baavgai

RE: SELECT DISTINCT

1 Jul, 2009 - 11:16 AM
Post #5

Dreaming Coder
Group Icon

Joined: 16 Oct, 2007
Posts: 4,350



Thanked: 411 times
Dream Kudos: 550
Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua, Cheese

My Contributions
QUOTE(xheartonfire43x @ 1 Jul, 2009 - 12:59 PM) *

I build a Drop-Down list with the query where the value is the ID and the display is the Name.


Hmm... If your data isn't dirty, this should yield the same number twice:
sql

SELECT count(distinct name), count(distinct id) FROM groups


In case it doesn't, this would work fine:

sql

SELECT min(name) AS name, id
	FROM groups
	GROUP BY id
	ORDER BY name
User is online!Profile CardPM
+Quote Post

xheartonfire43x

RE: SELECT DISTINCT

1 Jul, 2009 - 11:18 AM
Post #6

D.I.C Regular
***

Joined: 22 Dec, 2008
Posts: 267



Thanked: 2 times
My Contributions
QUOTE(kmangold @ 1 Jul, 2009 - 11:10 AM) *

Quickly thinking, here is a possible solution: instead of using ORDER BY name, try GROUP BY name.


My MSSQL server throws an error if I just change ORDER BY name to GROUP BY name. And if I do GROUP BY name,id I get the same return.
User is offlineProfile CardPM
+Quote Post

sherman

RE: SELECT DISTINCT

1 Jul, 2009 - 04:11 PM
Post #7

New D.I.C Head
*

Joined: 1 Jul, 2009
Posts: 1
QUOTE(xheartonfire43x @ 1 Jul, 2009 - 11:18 AM) *

QUOTE(kmangold @ 1 Jul, 2009 - 11:10 AM) *

Quickly thinking, here is a possible solution: instead of using ORDER BY name, try GROUP BY name.


My MSSQL server throws an error if I just change ORDER BY name to GROUP BY name. And if I do GROUP BY name,id I get the same return.


From what you stated, it seems "Name" is not unique. If you need to get a list of unique name, what would you like to do if duplicate name happen - pick one and ignore the other ? does it matter to you which id is select. If not, you could use
select name, min(id) -- or max(id) if you like
from ....
group by name
order by name
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/21/09 12:10PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month