Unique row Query Help

Question

Post reply

Unique row Query Help

rachel haez

Grasshopper

Points: 16
More actions
April 20, 2006 at 1:23 am

#111757

Hello All,
I am using Sql server 2000 and need help with the following query

I have a table from which I need to select the entire row except in the case of those rows which match by a certain column only one row with the first row must be brought back or displayed
for e.g.
create table test
(
col1 int,
col2 int,
col3 int,
col4 char(1)
)
insert into test values (1,2,3,'A')
insert into test values (1,2,3,'B')
insert into test values (4,5,6,'C')
insert into test values (7,8,9,'D')
select from the table “test” would yield the following results
col1        col2        col3        col4
----------- ----------- ----------- ----
1           2           3           A
1           2           3           B
4           5           6           C
7           8           9           D

The query I am looking for should return only
col1        col2        col3        col4
----------- ----------- ----------- ----
1           2           3           A
4           5           6           C
7           8           9           D
The row
col1        col2        col3        col4
----------- ----------- ----------- ----
1           2           3           B
should not be retrieved as there is already a row which matches the first three cols but only differs by col4 in which it is sufficient to bring first row
THANKS FOR ANY HELP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SELECT col1, col2, col3, MIN(col4)

FROM

GROUP BY col1, col2, col3

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Just a little warning... what do you mean by "first row"? SQL Server does not have any info about order of the rows. Sergiy wrote the query so that it returns the rows you mentioned in your example. It seems that you are looking for minimum value in col4... However, if the values A, B, C are just a dummy and you don't want to return the smallest value, then MIN(col4) will not work... In such case, please explain in more detail how to recognize the row that should be returned.

Riyaz Ghouse SSC-Addicted Points: 464 More actions · Answer 3

Group by col1, col2, col3 is the best option....

but watz ur fourth column...wat shd it return...

can you be more specific...for a specific answer.

Thanks

Riyaz

Riyaz Ghouse SSC-Addicted Points: 464 More actions · Answer 4

Group by col1, col2, col3 is the best option....

but watz ur fourth column...wat shd it return...

can you be more specific...for a specific answer.

Thanks

Riyaz