select distinct

Question

Hi,

Please could you advice?

Is posible to do distinct select from these? I am idiot thx a lot.

ltr_BarCode	ltr_DbtrN	smx_ID
C5117051	7048622	25747
C5117407	7016883	25585
C5119627	7025342	25489
C5119627	7025342	25490

But I need it in this way not only one or two columns. Please note that last column is not unique.

SELECT TempSent.*

FROM TempSent 

where ltr_BarCode

 IN (SELECT DISTINCT ltr_BarCode from  TempSent)

Answer 1

Hi Radim,

Why don't you group it as per ltr_bar_code if it has got no duplicate values.

One way of whowing this is like the following

SELECT * FROM TempSent
WHERE id IN
(SELECT MIN(id) FROM TempSent GROUP BY ltr_bar_code)

This will select only one row for each distinct ltr_bar_code, the row with the minimum id which is what your result seems to portray


Another way

 WITH Distinctbarcode AS
(
    SELECT ID,ltr_bar_code,ltr_DBTRN,SMX_ID,
        ROW_NUMBER() OVER(PARTITION BY ltr_bar_code ORDER BY ID) AS 'RowNum'
    FROM TempSent)


SELECT *
FROM TempSent
WHERE RowNum = 1

[ col1,col2,col3 are the columns you want to display ]

This works on SQL Server 2005 onwards

Now you can use other columns as well to select the records

Keep me posted, hope this helps you,mark it as an answer if it has helped you

Answer 2

Thx a lot,

But there is a unique value in smx_ID.