Select distinct data from one column

madkaikar_ashish · December 16th, 2004, 01:41 AM

I have a table 'wRelated' with the following columns

    [related_id] [int]
    [channel_id] [int]
    [mui] [varchar]
    [price_group_id]
    [type_id] [int]
    [related_mui] [varchar] (100)
    [date_started] [smalldatetime]
    [date_ended] [smalldatetime]
    [date_entered] [datetime]
    [deleted] [tinyint],
    [rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.

[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'

I have tried Unions and temporary table inserts.

ashish

jemacc · December 17th, 2004, 10:25 AM

Try using distinct

Here a is sample

Code:

SELECT DISTINCT related_id,channel_id,mui,price_group_id,type_id,related_mui,date_started,date_ended,date_entered,deleted,rank 
FROM wRealted
GROUP BY related_id,channel_id,mui,price_group_id,type_id,related_mui,date_started,date_ended,date_entered,deleted,rank

Jaime E. Maccou