Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 2nd, 2010, 10:27 AM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default using DISTINCT

Hi there

I have a number of identical values in the rows of one of my tables in my database

How do I only pull one of those out using DISTINCT?

This is my SQL but it doesn't seem to be working - there's no error but it doesn't just pull out 1 record for each card, it returns the whole lot still.

SELECT DISTINCT tbl_WebserviceLog.cardNumber, tbl_Club.gym, tbl_Account.clubid, tbl_Account.Name, tbl_Account.Surname, tbl_WebserviceLog.LogID, tbl_WebserviceLog.AccountID, tbl_WebserviceLog.Dated, tbl_WebserviceLog.credit, tbl_WebserviceLog.Type, tbl_WebserviceLog.returnVal FROM tbl_WebserviceLog, tbl_Account, tbl_Club WHERE tbl_WebserviceLog.AccountID = '521' AND tbl_WebserviceLog.AccountID = tbl_Account.AccountID AND tbl_Club.gymid = tbl_Account.clubid GROUP BY tbl_WebserviceLog.cardNumber,tbl_Club.gym, tbl_Account.clubid, tbl_Account.Name, tbl_Account.Surname, tbl_WebserviceLog.LogID, tbl_WebserviceLog.AccountID, tbl_WebserviceLog.Dated, tbl_WebserviceLog.credit, tbl_WebserviceLog.Type, tbl_WebserviceLog.returnVal ORDER BY tbl_WebserviceLog.Dated

Any help greatly appreciated.

thanks

Adam
Reply With Quote
  #2 (permalink)  
Old July 2nd, 2010, 08:48 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

SELECT DISTINCT will remove all ENTIRELY DUPLICATED rows. So if even ONE FIELD in your list of fields has even ONE CHARACTER difference between rows, then all rows will be returned.

Generally speaking, this means that if your SELECT list includes ID fields that are primary keys in any table except the first one, you *will* get duplicates.

Anyway, you should be able to just look carefully at the data being returned and find the (likely minor) differences yourself. If any field shows different values in different records, then you can *NOT* include that field in SELECT DISTINCT.

*************

Comment: Often, what you want isn't really SELECT DISTINCT. Often what you really want is the first (or last or...) record in a group where you get to select the characteristics of the group. If there is only one field that differs amongst the entire group, then instead of using SELECT DISTINCT, you can use MIN() or MAX() and GROUP BY. Example:
Code:
SELECT U.name, U.email, MAX(P.postingDate) AS lastPostingDate
FROM users AS U, postings AS P
WHERE U.userid = P.userid
GROUP BY U.name, U.email
But in more complex situations, you might not even be able to do that.

I won't bore you with methods you won't need, but if you think you need somethiing more advanced/complex, ask again.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
adamhw (July 5th, 2010)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Distinct Smeghead SQL Server 2005 9 December 17th, 2007 11:10 AM
Distinct Eddywardo SQL Server 2005 1 September 2nd, 2006 08:41 AM
Distinct data arnabghosh Access 2 September 21st, 2005 11:46 AM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 09:53 AM
Distinct or Group By? sprion Access 9 January 13th, 2005 09:31 PM



All times are GMT -4. The time now is 04:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.