Need output

Question

Need output

arup chakraborty

Hall of Fame

Points: 3640
More actions
September 22, 2009 at 4:24 am

#204882

Hi,
I need the name, value and maximum version number from the table.
create table #temp (id int identity(1,1), name varchar(2),
value varchar(10), version int)
insert into #temp values ('X', 'london', 1)
insert into #temp values ('X','rome',2)
insert into #temp values ('X','chennai',3)
insert into #temp values ('Y','kolkata',4)
insert into #temp values ('Y','blore',5)
insert into #temp values ('Y','delhi',6)
insert into #temp values ('Z','mumbai',7)
insert into #temp values ('Z','Hawrah',8)
insert into #temp values ('Z', 'sydney',9)
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

It is not entirely clear what you want to acheive but to give you a start ,this query will select the rows with the max version grouped for each name;

SELECT #temp.NAME, #temp.VALUE, #temp.VERSION FROM #temp

INNER JOIN (SELECT NAME,MAX(Version) AS Version FROM #temp GROUP BY [NAME]) a

ON #temp.NAME = a.[NAME] AND #temp.version = a.version

ORDER BY version

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi Steveb,

Thanks 4 ur reply.

This is exactly what I need.

Thanks again.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 3

Hi,

1 more question:

Shall I do the same with IN? (Plz dont ask why, its simply fro knowlwdge)

I have used the following query, but its giving syntax error:

select name, value, version from #temp

where name, version in (select name, max(version) as version from #temp group by name)

Thanks in advance.

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

You can do the same with IN

select name, value, version from #temp

where name in (select name as version from #temp group by name)

AND version in (select max(version) as version from #temp group by name)

But I perfer using a JOIN as it has a nicer execution plan.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 5

steveb. (9/22/2009)
You can do the same with IN
select name, value, version from #temp
where name in (select name as version from #temp group by name)
AND version in (select max(version) as version from #temp group by name)
But I perfer using a JOIN as it has a nicer execution plan.

Hi Steved,

I have some confusion reg. this IN. In this query, the conditions are not checked pair wise i.e. name ans max(version) are not checked pair wise. While in the JOIN query, they are checked pair wise.

I have also tested and in my example it is working fine. But I doubt it may produce some different result with some different dataset.

Can we include 2 column names as the parameter of IN? Is it possible?>

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

arup_kc (9/22/2009)
steveb. (9/22/2009)
You can do the same with IN
select name, value, version from #temp
where name in (select name as version from #temp group by name)
AND version in (select max(version) as version from #temp group by name)
But I perfer using a JOIN as it has a nicer execution plan.
Hi Steved,
I have some confusion reg. this IN. In this query, the conditions are not checked pair wise i.e. name ans max(version) are not checked pair wise. While in the JOIN query, they are checked pair wise.
I have also tested and in my example it is working fine. But I doubt it may produce some different result with some different dataset.
Can we include 2 column names as the parameter of IN? Is it possible?>

Yeah sorry i wrote that query without thinking about the data it will not return the correct results when more data is added.

When using IN with a sub-query the result has to have only One column.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 7

Steved,

Great help. So, there is no way apart from INNER join...anyway, that's fine for me.

Thanks again.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 8

Erm... there is another way - check out ROW_NUMBER, OVER clauses and the PARTITION BY statement.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 9

Matt Whitfield (9/22/2009)
Erm... there is another way - check out ROW_NUMBER, OVER clauses and the PARTITION BY statement.

Hi Matt,

Can u describe some more on those issue?

kabi Valued Member Points: 64 More actions · Answer 10

Select A.NAME, A.VALUE, A.VERSION from

(select row_number()over(partition by [name] order by VERSION desc ) as SlNo,* from #temp) as A

where SlNo=1

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 11

kabi (9/23/2009)
Select A.NAME, A.VALUE, A.VERSION from
(select row_number()over(partition by [name] order by VERSION desc ) as SlNo,* from #temp) as A
where SlNo=1

thumbs up

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone