Select rows with one distinct column help

f50bodykit · #1 (**permalink**) 08-05-04, 13:43

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

LKBrwn_DBA · #2 (**permalink**) 08-05-04, 14:51

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!

f50bodykit · #3 (**permalink**) 08-05-04, 15:25

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

LKBrwn_DBA · #4 (**permalink**) 08-05-04, 16:00

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

f50bodykit · #5 (**permalink**) 08-05-04, 16:49

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.

LKBrwn_DBA · #6 (**permalink**) 08-05-04, 20:42

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

r937 · #7 (**permalink**) 08-05-04, 20:48

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 )

f50bodykit · #8 (**permalink**) 08-05-04, 21:26

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.

f50bodykit · #9 (**permalink**) 08-05-04, 21:40

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?

LKBrwn_DBA · #10 (**permalink**) 08-06-04, 08:35

Is cdID unique?
And you need an alias:

Code:

...
FROM  (
            SELECT Max(cdID) cdID, Title FROM CMCD Group by Title
      ) TTL, ...

f50bodykit · #11 (**permalink**) 08-06-04, 12:22

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

Internet Services

Web Development

Digital Marketing

Consumer Tech