how to read unique records based a field that have duplicate values

Question

how to read unique records based a field that have duplicate values

DURGESH-769360

SSC Eights!

Points: 968
More actions
March 8, 2009 at 11:19 pm

#72693

hi all,
i have a table in which a column has duplicate values
eg
col1 col2
123 ram
124 ram
125 ravi
126 rahi
i need a query to select col1, col2 in which i need only one record w.r.t col2 as ram. so, my output should be
col1 col2
123 ram
125 ravi
126 rahi
can anybody help me to solve this issue
thanks in advance
regards
Durgesh J

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

You can do it by using a derived table (within the query) to get MIN of Col1 for each Col2 value and then doing an INNER JOIN of this table to the actual table on both the columns.

SELECTT1.Col1, T2.Col2

FROMSomeTable T1

INNER JOIN --This is the derived table that gets the first Col1 value for each Col2

(

SELECTCol2, MIN( Col1 ) AS LastCol1

FROMSomeTable

GROUP BY Col2

) T2 ON T1.Col2 = T2.Col2 AND T1.Col1 = T2.LastCol1

--Ramesh

DURGESH-769360 SSC Eights! Points: 968 More actions · Answer 2

hi ss

It works fine if col1 is integer, but if col1 is varchar then it is not working

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Can you post the table structure with sample data?

karthik

steveb. SSC-Forever Points: 46733 More actions · Answer 4

DURGESH (3/9/2009)
hi ss
It works fine if col1 is integer, but if col1 is varchar then it is not working

You will need to convert col1 to an INT use;

CAST(Col1 AS INT)

DURGESH-769360 SSC Eights! Points: 968 More actions · Answer 5

hi

if my table is as follows

col1 col2

aa ram

bb ram

cc ravi

dd rahi

and i need the output as

col1 col2

bb ram

cc ravi

dd rahi

karthik M One Orange Chip Points: 29139 More actions · Answer 6

create table test

(

col1 varchar(10),

col2 varchar(10)

)

go

insert into test

select 'aa','ram'

union all

select 'bb','ram'

union all

select 'cc','ravi'

union all

select 'dd','rahi'

go

select max(col1)as col1,col2
from test
group by col2

output:

col1 col2

ddrahi

bbram

ccravi

karthik

DURGESH-769360 SSC Eights! Points: 968 More actions · Answer 7

hi

if my table is as follows

col1 col2 col3

aa ram b

bb ram a

cc ravi c

dd rahi d

and i need the output as

bb ram b

cc ravi c

dd rahi d

that is order by col3

if i use

select max(col1) as col1,col2 from tbl group by col2 order by case when col3='b' then 1

when col3='a' then 2 end

then it gives an error col3 is not mentioned in group by or select list

so, how can i write the query

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

DURGESH (3/9/2009)
hi
if my table is as follows
col1 col2 col3
aa ram b
bb ram a
cc ravi c
dd rahi d
and i need the output as
bb ram b
cc ravi c
dd rahi d
that is order by col3
if i use
select max(col1) as col1,col2 from tbl group by col2 order by case when col3='b' then 1
when col3='a' then 2 end
then it gives an error col3 is not mentioned in group by or select list
so, how can i write the query

Like this...create table #test

(

col1 varchar(10),

col2 varchar(10),

col3 varchar(10) -- new column

)

go

insert into #test

select 'aa','ram', 'b'

union all

select 'bb','ram', 'a'

union all

select 'cc','ravi', 'c'

union all

select 'dd','rahi', 'd'

go

select max(col1)as col1, col2, max(col3)

from #test

group by col2

order by col1

(thanks for setting up the sample data, Karthik)

The problem with this solution is, although it generates the result set you describe, something tells me that it's unlikely to be correct - there are bits and pieces of various rows aggregated into less rows with no properly-described logic.

Are you looking for some hints about the usage of aggregates, or is this a real world problem? If it's the latter, then some more information and significantly more sample data would be very helpful.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 9

I agree with Chris, I don't think that this looks like a real world scenario. Or may be you have posted a simpler version of the scenario. If you can provide the actual scenario with some sample data, may be we can provide you a much better solution.

--Ramesh