Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unanswered: Select rows with one distinct column help

    Hello,

    I need a query that will return rows that are distinct by one Column(CD.Title). I am using two tables User and CD. The results look something like this when I perform a select joining the two.

    CD.ID| CD.userID | CD.Title | CD.Description| User.Name|
    1 40 xxxx zzzz Jason
    2 40 yyyy aaaa Jason
    3 40 xxxx nnnn Jason
    4 40 xxxx xxxxx Jason

    The Query looks like this
    SELECT CD.cdID, CD.UserID, CD.Title, CD.Description, User.Name FROM User,CD WHERE CD.UserID = User.userID AND CD.UserID = 40


    How do I get my query to return only

    CD.ID| CD.userID | CD.Title | CD.Description| User.Name|
    2 40 yyyy aaaa Jason
    4 40 xxxx xxxxx Jason

    I only want it to return rows that contain the last CD.Title that is unique. Im using MSSQL.

    Any help is greatly appreciated.

    Thank you,

    Jason

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Use MIN/MAX functions

    It depends on which CD.Description you want, how do you know which is the last? Is there an order different from ROWID?:

    Use the MIN or MAX function:
    Code:
    SELECT CD.cdID, CD.UserID, CD.Title
    , MAX(CD.Description) Desciption, User.Name 
    FROM User,CD WHERE CD.UserID = User.userID AND CD.UserID = 40
    GROUP BY CD.cdID, CD.UserID, CD.Title, User.Name


    PS: ROWID order may not be the order in which the rows were created!
    Last edited by LKBrwn_DBA; 08-05-04 at 15:53.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2004
    Posts
    6
    Hello

    Thank you your reply is very much appreciated. I tried the query below and obtained the same results. I have duplicate Titles returned for a particular user.

    Im using the results to get a unique list of cd titles by user name. So that if Jason requested (CD1, CD2, CD1, CD2) in the past he will be able to select just
    CD1
    CD2
    from a listbox instead of
    CD1
    CD2
    CD1
    CD2.

    Im ordering by a unique RowID, and I want the last row with the duplicate title.

    Code:
    SELECT
    
    	CMCD.cdID,
    	CMCD.userid, 
    	Max(CMCD.title) Title, 
    	CMCD.instructions, 
    	CMCD.volumeLabel, 
    	CMCD.dateRequested, 
    	CMCD.dateStarted, 
    	CMCD.dateCompleted, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUser.name, 
    	CMUser.email, 
    	CompletedByUser.name As CompletedByUserName
    FROM
    	CMCD, 
    	CMUser, 
    	CMUser As CompletedByUser
    WHERE
    	CMCD.userID = CMUsers.userid
    AND
    	CMCD.completedbyid = CompletedByUser.userid
    AND
    	CMCD.userID = 1
    
    GROUP BY 
                 CMCD.cdID,
    	CMCD.UserID,
    	CMCD.instructions,
    	CMCD.volumeLabel,
    	CMCD.dateRequested,
    	CMCD.dateStarted,
    	CMCD.dateCompleted, 
    	CMCD.buildID, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUser.name, 
    	CMUser.email,
    	CompletedByUser.Name

    When a user clicks on CD1 in the listbox it will populate a form with all of the information from CD1

    Thank you for your help.

    Jason

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    The problem seems to be that not only the title is duplicate, but maybe some of the rest of the colums also?

    Try this, see what you get:
    Code:
    SELECT
    	CMCD.cdID,
    	CMCD.userid, 
    	TTL.Title, 
    	CMCD.instructions, 
    	CMCD.volumeLabel, 
    	CMCD.dateRequested, 
    	CMCD.dateStarted, 
    	CMCD.dateCompleted, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUser.name, 
    	CMUser.email, 
    	CompletedByUser.name As CompletedByUserName
    FROM  (
        SELECT Distinct cdID, userid, title
          FROM CMCD
          ) TTL,
    	CMCD, 
    	CMUser, 
    	CMUser As CompletedByUser
    WHERE    CMCD.userID = TTL.userid
        AND    CMCD.cdID   = TTL.cdID
        AND	CMCD.userID = CMUsers.userid
        AND	CMCD.completedbyid = CompletedByUser.userid
        AND	CMCD.userID = 1
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2004
    Posts
    6
    Hi,

    I tried the query but obtained the same results. What I want to do is perform

    SELECT DISTINCT CMCD.title FROM CMCD WHERE CMCD.userID = 1

    Get as results
    CD1
    CD2

    perform
    a loop on

    SELECT TOP(1) * FROM CMCD, CMUser WHERE CMCD.userID = CMUser.userID AND
    CMCD.Title= Result1

    All in one query.

    Thank you again for your help. Im trying to figure out what the statements you have given me thus far do, to see if I can change them a bit and possibly give me the results I want.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question Sync request

    As I understand it, the requirements are:

    1) Return unique cd titles per customer.
    2) Include information of the latest corresponding transaction.

    If this is the case, execute the following queries and let me know which returns the result you require, (excluding the last transactional data) for :
    1)
    Code:
    SELECT Distinct cdID, userid, title
          FROM CMCD Where userID = 1
    or
    2)
    Code:
    SELECT Distinct userid, title
          FROM CMCD Where userID = 1
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's how to combine the two queries in the last post

    this may not return anything useful, because it returns rows for all users -- i'm just answering the question about how to combine those two queries and avoid the loop

    Code:
    SELECT TOP 1 * 
      FROM CMCD
         , CMUser 
     WHERE CMCD.userID 
         = CMUser.userID 
       AND CMCD.Title
        in ( SELECT DISTINCT CMCD.title 
               FROM CMCD WHERE CMCD.userID = 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2004
    Posts
    6
    I somewhat figured out what you did with the second reply you gave.

    Number two on your last reply works

    Code:
        SELECT Distinct userid, title
          FROM CMCD Where userID = 1
    But I need a way to associate it with the first part of the query. Is there a way to get

    Code:
    SELECT Distinct cdID, userid, title
          FROM CMCD Where userID = 1
    To return destinct titles so if I have

    cdID | userID | title

    1 40 xxxxx
    2 40 yyyy
    3 40 xxxxx
    4 40 xxxxx

    the query returns

    1 40 yyyyy
    4 40 xxxxxx

    I believe I would then be able to use the query you gave me earlier(below) to filter out the results to where TTL.cdID = CMCD.cdID.


    Code:
    SELECT
    	CMCD.cdID,
    	CMCD.userid, 
    	TTL.Title, 
    	CMCD.instructions, 
    	CMCD.volumeLabel, 
    	CMCD.dateRequested, 
    	CMCD.dateStarted, 
    	CMCD.dateCompleted, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUser.name, 
    	CMUser.email, 
    	CompletedByUser.name As CompletedByUserName
    FROM  (
        SELECT Distinct cdID, userid, title
          FROM CMCD
          ) TTL,
    	CMCD, 
    	CMUser, 
    	CMUser As CompletedByUser
    WHERE    CMCD.userID = TTL.userid
        AND    CMCD.cdID   = TTL.cdID
        AND	CMCD.userID = CMUsers.userid
        AND	CMCD.completedbyid = CompletedByUser.userid
        AND	CMCD.userID = 1
    Thanks for you help you guys are awesome. Even if it doesnt get figured out im still learning.

  9. #9
    Join Date
    Aug 2004
    Posts
    6

    figured it out kind of

    I found that

    Code:
    SELECT Max(cdID), Title FROM CMCD Group by Title
    answers my last question

    I tried to do this but I get an error in sql analyzer

    Code:
    SELECT
    	CMCD.cdID,
    	CMCD.userid, 
    	TTL.Title, 
    	CMCD.instructions, 
    	CMCD.volumeLabel, 
    	CMCD.dateRequested, 
    	CMCD.dateStarted, 
    	CMCD.dateCompleted, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUsers.name, 
    	CMUsers.email, 
    	CompletedByUser.name As CompletedByUserName
    FROM  (
                SELECT Max(cdID), Title FROM CMCD Group by Title
          ) TTL,
    	CMCD, 
    	CMUsers, 
    	CMUsers As CompletedByUser
    WHERE    
        CMCD.cdID   = TTL.cdID
        AND	CMCD.userID = CMUsers.userid
        AND	CMCD.completedbyid = CompletedByUser.userid
        AND	CMCD.userID = 1
    the error is "no column was specified for column 1 of TTL"

    do you have any suggestion?

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Smile

    Is cdID unique?
    And you need an alias:
    Code:
    ...
    FROM  (
                SELECT Max(cdID) cdID, Title FROM CMCD Group by Title
          ) TTL, ...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Aug 2004
    Posts
    6

    You Rock!!!!!

    Awesome!!! Thanks for you help, works great. cdID is unique. Here is the final code.


    Code:
    SELECT
    	CMCD.cdID,
    	CMCD.userid, 
    	TTL.Title, 
    	CMCD.instructions, 
    	CMCD.volumeLabel, 
    	CMCD.dateRequested, 
    	CMCD.dateStarted, 
    	CMCD.dateCompleted, 
    	CMCD.status, 
    	CMCD.CompletedByID, 
    	CMUser.name, 
    	CMUser.email, 
    	CompletedByUser.name As CompletedByUserName
    FROM  (
                SELECT Max(cdID) cdID, Title FROM CMCD Group by Title
          ) TTL,
    	CMCD, 
    	CMUser, 
    	CMUser As CompletedByUser
    WHERE    
        CMCD.cdID   = TTL.cdID
        AND	CMCD.userID = CMUsers.userid
        AND	CMCD.completedbyid = CompletedByUser.userid
        AND	CMCD.userID = 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •