how to select dis distinct rows based on (1 col value ) importants

moayyad

SOLVED CLOSED ACTIVE ANSWERED
User: moayyad
Posted: on Apr 26, 2016 04:15 PM
Forum: .Net Basics
Answer: 1
Views: 3780

hi all

i have a table with 10 cols

first col is code

and my table may contain a data with same code and other cols deferent like

code name cat date ..... amount

1 ahmad 1 01/01/2004 10.2

1 ahmad 1 01/01/2010 10.5

2 mohammad 01/01/2004 10

2 mohammad 01/01/2004 20

now i want sql query to select just non duplicate rows based on code

like this ( if code selected ignore others same code values)

1 ahmad 1 01/01/2004 10.2

2 mohammad 01/01/2004 10

Shashika...

0 3

Reply
ANSWER
User: Shashikant
Replied: on Apr 27, 2016 02:05 AM
Report

Hi,

Please refer below code

SQL

DECLARE @temp as TABLE(code INT,name VARCHAR(50),cat INT,[date] DATETIME,amount DECIMAL(18,2))

INSERT INTO @temp VALUES(1,'ahmad',1,'01/01/2004',10.2)
INSERT INTO @temp VALUES(1,'ahmad',1,'01/01/2010',10.5)
INSERT INTO @temp VALUES(2,'mohammad',NULL,'01/01/2004',10)
INSERT INTO @temp VALUES(2,'mohammad',NULL,'01/01/2004',20)

SELECT code
	  ,name
	  ,cat
	  ,[date]
	  ,amount 
FROM 
(
	SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY (SELECT 0)) as RowNumber,*
	FROM @temp
) as tbl
WHERE tbl.RowNumber = 1

Screenshot

how to select dis distinct rows based on (1 col value ) importants

Company

Explore

Follow us

Contact Us