Help with select MS Sql Select statement...

Question

Hello,

I have a table like the following table:

UserID  Num1    Num2    Code    Name    Cat
7293    32:16.0 50:22.0 3   Jim 33
7293    32:16.0 59:28.0 4   Jim 12
7316    32:16.0 55:32.0 4   Karen   33
7316    32:16.0 28:31.0 4   Karen   7
7287    32:16.0 01:27.0 2   Mike    33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28
7302    32:17.0 01:26.0 4   Bill    10
7302    32:17.0 01:26.0 3   Bill    32

and I am trying to select unique rows. I need the output to be this:

UserID  Num1    Num2    Code    Name    Cat
7293    32:16.0 50:22.0 3   Jim 33
7316    32:16.0 28:31.0 4   Karen   7
7287    32:16.0 01:27.0 2   Mike    33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28

I am no SQL expert so any help would be greatly appreciated.

I tried using a SELECT statement with DISTINCT, but that only gives me one column, I need the entire row. If I put multiple fields in the SELECT with the DISTINCT it returns all the rows.

Thanks for the assistance.

Ok, to answer some of the replies I recieved:

1) I have tried many queries, but all based around: SELECT DISTINCT UserID FROM MyTable SELECT DISTINCT * FROM MyTable SELECT DISTINCT UserID, Num1, Num2, Code, Name, Cat FROM MyTable

Again, I am not a SQL Expert and everything I read about DISTINCT there is no real example of whatI need.

2) However, reading the replies gave me a thought, since I really only need a few columns, such as UserID, Name,

Can I use DISTINCT and specify those columns only? Hmm, I wil give it a try.

3) I am not sure what the Min, Max stuff is in the replies, but I will try it and hopefully in the process gain a better understanding of the SQL syntax.

Thanks again for the help.

Answer 1

I don't know if it is the best way, but one way is to use grouping:

select UserId, min(Num1), min(Num2), min(Code), min(Name), min(Cat)
from tbl group by UserId

Well, this will give you Jim 12 instead of Jim 33, but I guess you can figure out an aggregate function that gives you the right row.

Answer 2

you need to aggregate the fields you don't want to group on, like:

select UserID,  min(Num1),  min(Num2), min(Code), min(Name), max(Cat)
from tbl
group by userID

Answer 3

1) If this is homework, please mark it as such, and then show some of your prior attempts (people are much more willing to help those who have put in honest effort and are stuck, as opposed to looking for free answers). You WILL get more respect, help, and credit if you've shown that you've exhausted every possibility you can think of.

2) Is the exact input and exact output correct? It strikes me as strange that Karen's Cat was 7 and not 33 in the 'answer'. Each other user has their first value selected.

Answer 4

It would appear that you want the "TOP 1" result for each UserID.

To do so on the code side would be easier than SQL side but it is possible.

One such way is to Generate a list of distinct UserID's:

SELECT DISTINCT UserID FROM @YourTable

Use a cursor to iterate over each ID and insert each "TOP 1" record into a temp table:

DECLARE @TempTable TABLE (
    UserID INT, 
    Num1 VARCHAR(10), 
    Num2 VARCHAR(10), 
    Code INT, 
    Name VARCHAR(10), 
    Cat INT
)

DECLARE @ID INT
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT UserID FROM @YourTable

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @TempTable
    SELECT TOP 1 * FROM @YourTable WHERE UserID = @ID   

    FETCH NEXT FROM MyCursor
    INTO @ID
END

CLOSE MyCursor
DEALLOCATE MyCursor

Now, your desired records should be in @TempTable

SELECT * FROM @TempTable

7287    32:16.0 01:27.0 2   Mike    33
7293    32:16.0 50:22.0 3   Jim 33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28
7316    32:16.0 55:32.0 4   Karen   33

*Note: My datatype mappings probably don't match yours.

Answer 5

This query makes no assumption as to what ranks as the row from each user you want. To rank the query to accept the 'certain' top value you will have to alter the order by clause in the 'OVER' statement for the 'ROW_NUMBER()' function

with partTable (rw_num,UserID,Num1,Num2,Code,Name,Category) as 
    (select 
    ROW_NUMBER() over(partition by UserID order by UserID) as rw_num
    ,UserID
    ,Num1
    ,Num2
    ,Code
    ,Name
    ,Category
    from table_1)
    select 
    UserID
    ,Num1
    ,Num2
    ,Code
    ,Name
    ,Category
    from partTable where rw_num = 1