Distinct for different columns

Hi everebody.
I need your advice, suppose i have 4 colums
col1, col2, col3, col3
e.g.
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
aa | 1 | 2 | 1 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
so on ...
i need to distinct col2 so i my result set will be
col1 | col2 | col3 | col4 |
aa | 1 | 3 | 4 |
bb | 2 | 2 | 1 |
cc | 3 | 2 | 1 |
it's not important which row not display
any idea' thanks in advance
Message posted via http://www.webservertalk.comWhat is the Primary Key? Please post proper DDL rather than sketches of
tables, otherwise we can only guess. Here's my guess:
SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
Depending on your key there's probably a better way. However, I'm
always suspicious of requirements that say "show me some row, I don't
care which". To me, this indicates that the data model is incorrect -
if all rows of a set are equally valid then apparently the table is
carrying redundant data, which ought to be eliminated.
David Portas
SQL Server MVP
--|||Thanks, form now u can play lotto, because u r guessed right.
However can u please explain me your examle, i'm not really understand it.
Thanks
Message posted via http://www.webservertalk.com|||SELECT DISTINCT col1, col2, col3, col4
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM
(SELECT TOP 1 col1, col2, col3, col4
/* Get one row (TOP) for each value of
col2 in the outer (T1) query */
FROM YourTable
WHERE col2 = T1.col2
ORDER BY col1, col2, col3, col4) AS T2
WHERE T1.col1 = T2.col1
/* Is the row in T1 the same as the one
we got from the TOP subquery? */
AND T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col4 = T2.col4)
David Portas
SQL Server MVP
--|||Thanks i got it .
P.S. the SELECT was from view and view have a lot of UNION from differents
tables, beacuse of that idon't told u what the primary key
Message posted via http://www.webservertalk.com|||Is it possible to perform exactly this funcionality without the 2 select
statements ?
Message posted via http://www.webservertalk.com|||In that case you'll probably find it much more efficient to do a join
between the base tables rather than query the view.
David Portas
SQL Server MVP
--|||>From my first post: "Please post proper DDL rather than sketches of
tables, otherwise we can only guess."
See: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I understand, but i need to use it a lot of times, that why i decided to
use view, it's big view and after i perform a lot of WHERE ... on this view
Message posted via http://www.webservertalk.com