## 4 replies

Last post Nov 01, 2016 11:29 PM by farhank

• pramuk97

Member

89 Points

243 Posts

### How to select one row from multiple rows

We have columns A,B,C,D,E and we want to filter data on the basis of E & G condition is only one E which is latest on the basis of E

Result should be

 A B C D E F G 7 150 115139 1712910 1099684 1026877 10/28/2016 16 150 115139 1712910 1099676 1026877 10/31/2016 70 150 115139 1712910 1099679 1026877 11/3/2016 60 150 115139 1712910 1099653 1026877 11/6/2016

Table Data

 A B C D E F G 6 150 115132 1695418 1085184 1012542 10/27/2016 7 150 115139 1712910 1099684 1026877 10/28/2016 12 150 115139 1712910 1099676 1026877 10/29/2016 14 150 115139 1712910 1099676 1026877 10/30/2016 16 150 115139 1712910 1099676 1026877 10/31/2016 18 150 115139 1712910 1099679 1026877 11/1/2016 20 150 115139 1712910 1099679 1026877 11/2/2016 70 150 115139 1712910 1099679 1026877 11/3/2016 72 150 115139 1712910 1099653 1026877 11/4/2016 58 150 115139 1712910 1099653 1026877 11/5/2016 60 150 115139 1712910 1099653 1026877 11/6/2016

http://www.pramuk97.blogspot.com/
• senthilwaits

Contributor

3290 Points

866 Posts

### Re: How to select one row from multiple rows

Try this

`select colA, max(colB) from test group by colA`

Regards,

Senthil Kumar Sundaram
• pramuk97

Member

89 Points

243 Posts

### Re: How to select one row from multiple rows

#### senthilwaits

Try this

`select colA, max(colB) from test group by colA`

I modified my question. please have a look.

http://www.pramuk97.blogspot.com/
• senthilwaits

Contributor

3290 Points

866 Posts

### Re: How to select one row from multiple rows

You can try this

```WITH numbered AS
( SELECT A, B, C, D, E, F, G,
ROW_NUMBER() OVER (PARTITION BY [A] ORDER BY [E] DESC) AS rownum
FROM test
)
SELECT A, B, C, D, E, F, G
FROM numbered
WHERE rownum = 1;```

Please don't change the original question.  it will confuse the people who are looking into the thread.  You can always reply to the thread or start a new thread

Regards,

Senthil Kumar Sundaram
• FarhanK

Contributor

2101 Points

384 Posts

### Re: How to select one row from multiple rows

Hi,

You can try the code below.

@@Senthilwaits Partition By columns are incorrect in your script. I Just fixed them.

```Declare @Tab Table (A Int, B Int, C Int, D Int, E Int, F Int, G date)
Insert Into @Tab
Select 6,	150,	115132,	1695418,	1085184,	1012542,	'10/27/2016' Union
Select 7,	150,	115139,	1712910,	1099684,	1026877,	'10/28/2016' Union
Select 12,	150,	115139,	1712910,	1099676,	1026877,	'10/29/2016' Union
Select 14,	150,	115139,	1712910,	1099676,	1026877,	'10/30/2016' Union
Select 16,	150,	115139,	1712910,	1099676,	1026877,	'10/31/2016' Union
Select 18,	150,	115139,	1712910,	1099679,	1026877,	'11/1/2016'  Union
Select 20,	150,	115139,	1712910,	1099679,	1026877,	'11/2/2016'  Union
Select 70,	150,	115139,	1712910,	1099679,	1026877,	'11/3/2016'  Union
Select 72,	150,	115139,	1712910,	1099653,	1026877,	'11/4/2016'  Union
Select 58,	150,    115139,	1712910,	1099653,	1026877,	'11/5/2016'  Union
Select 60,	150,	115139,	1712910,	1099653,	1026877,	'11/6/2016'

Select	T1.*
From	@Tab T1
Inner Join (
Select	MAX(G) G, E
From	@Tab
Group By E
) T2 On T1.G = T2.G And T1.E = T2.E
Order By T1.G

--- OR -----
;
WITH numbered AS (
SELECT	A, B, C, D, E, F, G,
ROW_NUMBER() OVER (PARTITION BY [E] ORDER BY [G] DESC) AS rownum
FROM @Tab
)
SELECT A, B, C, D, E, F, G
FROM numbered
WHERE rownum = 1
Order By G	```