Jump to content



Photo

[SQL] Select multiple columns with only one distinct column


  • Please log in to reply
6 replies to this topic

#1 Code149

Code149

    Neowinian

  • Joined: 11-June 10
  • Location: Iran

Posted 07 February 2011 - 16:28

How do you select multiple columns from a table while ensuring that one specific column doesn't contain duplicate values?

SELECT DISTINCT col1,col2,col3,col4 from table

This doesn't work, because DISTINCT here applies to all columns so columns as a whole are distinct. But I want a single column to have distinct values.

I'm using SQL Server. :)


#2 kjordan2001

kjordan2001

    Mystery Solver

  • Tech Issues Solved: 1
  • Joined: 27-May 02

Posted 07 February 2011 - 17:03

You're looking for GROUP BY. If you wanted to only have the value for col1 show up once in the dataset:
SELECT col1,col2,col3,col4 from table GROUP BY col1


Note that this will compact all the results that would have been display into one row. So you'll end up having to use sums or some other aggregate function to display the correct values if you can expect a different value in a row each time it shows up. Otherwise it will just pick the value from the first row.

#3 OP Code149

Code149

    Neowinian

  • Joined: 11-June 10
  • Location: Iran

Posted 07 February 2011 - 19:32

Thanks for your answer, it actually does serve my purpose. :)

This is the query I ran:

SELECT col1,max(col2), max(col3), max(col4) from table GROUP BY col1
Max functions used here don't mean much, they'er just there to pick the biggest value of the column in the row set.

As I said, in this case it serves my purpose. But what if there were, let's say 4 or more rows with a common col1 value, and you wanted to retrieve columns of some specific middle row among the grouped rows; How would you go about that?

I'm thinking cursors can be one way to do that, right?

#4 kjordan2001

kjordan2001

    Mystery Solver

  • Tech Issues Solved: 1
  • Joined: 27-May 02

Posted 09 February 2011 - 01:34

Thanks for your answer, it actually does serve my purpose. :)

This is the query I ran:

SELECT col1,max(col2), max(col3), max(col4) from table GROUP BY col1
Max functions used here don't mean much, they'er just there to pick the biggest value of the column in the row set.

As I said, in this case it serves my purpose. But what if there were, let's say 4 or more rows with a common col1 value, and you wanted to retrieve columns of some specific middle row among the grouped rows; How would you go about that?

I'm thinking cursors can be one way to do that, right?


I believe you could (SQL Server is not my strongest database skill), but if you can it would be better if you can weed out the row you want using the WHERE clause of the SQL statement.

#5 BGM

BGM

    Wibble Wobble™

  • Joined: 30-March 03
  • Location: Farnborough, UK

Posted 12 February 2011 - 23:56

this is no concept of 'middle' in MSSQL table, or any other? so it's a bit tricky..

can you give more information on your requirement?

where there's a will, there's a way! :D

#6 spartyjohnson

spartyjohnson

    I'm Spartacus

  • Joined: 18-April 03
  • Location: Proud to be American

Posted 13 February 2011 - 00:05

It sounds like you need to do something like the following:

select
* --should specify your specific columns
from table1 t1
inner join 
(select DISTINCT distinctColumn from table) t2
on t1.col = t2.distinctColumn

This code should get your distinct column and then join on it from another (or even the same) table.

#7 OP Code149

Code149

    Neowinian

  • Joined: 11-June 10
  • Location: Iran

Posted 18 February 2011 - 21:45

It sounds like you need to do something like the following:

select
* --should specify your specific columns
from table1 t1
inner join 
(select DISTINCT distinctColumn from table) t2
on t1.col = t2.distinctColumn

This code should get your distinct column and then join on it from another (or even the same) table.

No, doesn't work. It returns duplicate rows.

It doesn't matter tho, as my problem is solved, even ignoring the kjordan2001's answer. I just needed to omit those non-duplicate valued columns from the select list of a distinct clause, which were trivial anyways. The result set would then only include entirely distinct rows. And FYI, the table in question is named Customer and is part of the AdventureWorks2008R2 sample databases.

Thanks for answers though. :)