SQL - Distinct One Col, Select Multiple other?

Question

I've been trying to understate the best way to write an MS SQL 2005 query which pretty much does the following...

select distinct col1, col2, col3
from table1

Basically I want to perform Distinct on col1 but I dont want Dististinc on Col2/3, I just want there values.

I understand its not possible to write the query this way as I think I read the Distinct is applied to the row and not the col?

Could anyone please point me in the right direction? I did try right joing the values back in but this didnt work as I had to specify the extra joined cols in the distinct select which in turn performed the distinct on these, i.e..

select distinct t1.col1, t2.col2, t3.col3
from table1 t1
right join (select col1, col2, col3 from table1) t2
on t1.col1 = t2.col1

Edited to explain better..

select distinct t1.Hostname, t2.IP, t2.ActionDateTime, t2.Action
from tblUserActions t1
right join (select Hostname, IP, ActionDateTime from tblUserActions) t2
on t1.Hostname = t2.Hostname

Basically this table is a list of thousands of user actions and im trying to list distinct on the Hostname so I should only receive say 10 rows as thats how many different Hostnames there are. Then based on these hostnames I want to also join the most recent record data to the rows returned, so I'd like to return:

  Hostname, IP, ActionDateTime, Action
1 Host1, 165.123.123.1, 2012-06-14 02:07:08, Logon
2 Host2, 165.123.123.2, 2012-06-14 03:07:08, Logoff
3 Host3, 165.123.123.3, 2012-06-14 04:07:08, Logon
4 Host4, 165.123.123.4, 2012-06-14 05:07:08, Logoff
etc...

Any help/pointers would be great! Cheers.

If I understand it correct you want to select only the distinct values from col1, but you want everything from col2 and col3? When col1 has the same value, what do you want to show in its place? You have to have some value (even if it is null) showing up in col1 on the result set. — Lex, Jun 14 '12 at 10:32
Can you show us some sample data? I'm not sure I understand completely what you are after — , Jun 14 '12 at 10:34
This is impossible. Try it on paper - write a column of the same value for col1 and next to it a column with different values for col2. Which value of col2 you gonna take? If you want all of them, you cannot take only one of col1. What do you need this for? — Nikola Markovinović, Jun 14 '12 at 10:34
If you have two rows `(1, a, x)` and `(1, b, y)`, what results do you want? You could have `col1, MAX(col2), MIN(col3)` to give `(1, b, x)`, but you don't really explain what you want. Could you give some example input data and some example output data? — MatBailie, Jun 14 '12 at 10:37
Sorry for the rubbish explanation originally, I have updated my original post to show what I was meaning :) — Developr, Jun 14 '12 at 11:06

score 1 · Accepted Answer · answered Jun 14 '12 at 11:09

By the sounds of it I think this is what you are after:

WITH CTE AS
(   SELECT  HostName,
            IP,
            ActionDate,
            Action,
            ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY ActionDate DESC) AS RowNumber
    FROM    Table
)
SELECT  HostName,
        IP,
        ActionDate,
        Action
FROM    CTE
WHERE   RowNumber = 1

This will return only unique values for host name, then the values returned the other columns are based on the ORDER BY clause in the ROW_NUMBER() Windowed function.

You may need to alter the ORDER BY to suit your exact reqirements, I assumed latest action was probably the most likely.

score 1 · Answer 2 · answered Jun 14 '12 at 11:16

do you just want the latest action for each hostname/ip?

you could do that something like this:

with latestAction as (
select  hostname,
        ip,
        max(ActionDate) as latestActionDate
from    tblUserActions
group by hostname,
        ip)
select  la.hostName,
    la.ip,
    tua.ActionDate,
    tua.Action
from    tblUserActions tua join latestAction la on
        tua.hostname = la.hostname and
        tua.ip = la.ip and
        tua.ActionDate = la.latestActionDate

SQL - Distinct One Col, Select Multiple other?

2 Answers2

Linked