Distinct For Only Some Columns In A Row

Hello,
I'm stuck, and I have earnestly searched for an answer to this problem
during the past few hours...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
UserID MiscID
10 105
11 109
I would appreciate any assistance.
Thanks,
JimSELECT T1.UserID, Min(T2.MiscID) As MiscId
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jim Little" <123@.yahoo.com> wrote in message
news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
> Hello,
> I'm stuck, and I have earnestly searched for an answer to this problem
> during the past few hours...
> I'm trying to work with this query:
> SELECT T1.UserID, T2.MiscID
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> Based on the data in the tables, this query generates the following
> result:
> UserID MiscID
> 10 105
> 10 107
> 11 109
> 11 120
> 11 122
> The problem is that I do not want duplicate UserID's (I show only 10 and
> 11, but the table actually has many rows with duplicate UserID values).
> Instead, I want unique (distinct) UserID's and the lowest MiscID for each
> row. I would therefore like the result set to look like this:
> UserID MiscID
> 10 105
> 11 109
> I would appreciate any assistance.
> Thanks,
> Jim
>
>|||Thank you very much. That worked perfectly!
Although I made a "stripped-down" version of the problem so I could post a
focussed question, I actually need to join across four table and draw data
from each of them. However, I still want to "group" on only the first UserID
column. There are 12 additional columns that need to appear in the output.
I'll experiment a bit further and I'll post another follow-up message on
this thread if I get stuck again.
Meanwhile, thanks for the quick and helpful reply.
-- Jim
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uYpCUn8GFHA.2156@.TK2MSFTNGP09.phx.gbl...
> SELECT T1.UserID, Min(T2.MiscID) As MiscId
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> GROUP BY T1.UserID
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Jim Little" <123@.yahoo.com> wrote in message
> news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
>|||OK--I found a way to better express the problem I was having, but I need
first to add another column...
The Group By suggested by Roji does give me the following as output:
UserID MiscID
10 105
11 109
And that's what I wanted in my first question. But I had simplified my
question. In actuality, I require additional columns in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||I have posted a new message entitled "Distinct and Group By".
Thank you,
-- Jim