February 18, 2010 at 6:07 am
Hi,
I need a query which retrieves the data in this format.
Given below is the temp table and data.
create table #temp(Id int, name varchar(100), address varchar(100), Total Int)
insert into #temp values (1,'Name1','Add1',1)
insert into #temp values (1,'Name2','Add2',2)
insert into #temp values (2,'Name3','Add3',3)
insert into #temp values (2,'Name4','Add4',4)
insert into #temp values (2,'Name5','Add5',5)
insert into #temp values (3,'Name6','Add6',6)
Now i need each value of ID field to appear only once in the resultset with the first record and excluding the remaining rows.
For eg. From the above data, the output must be :
------------------------------
|ID| Name| Address| Total|
------------------------------
|1| Name1| Add1| 1| --First row of ID 1
|2| Name3| Add3| 3| --First row of ID 2
|3| Name6| Add6| 6| --First row of ID 3
Could you please help me out.
Regards,
Aditya
February 18, 2010 at 6:11 am
The depends on what you mean by the "first" record. I have ordered by the total column but if that isn't what you need then you can change it.
create table #temp(Id int, name varchar(100), address varchar(100), Total Int)
insert into #temp values (1,'Name1','Add1',1)
insert into #temp values (1,'Name2','Add2',2)
insert into #temp values (2,'Name3','Add3',3)
insert into #temp values (2,'Name4','Add4',4)
insert into #temp values (2,'Name5','Add5',5)
insert into #temp values (3,'Name6','Add6',6)
;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY total) AS rownum
FROM #temp
)
SELECT * FROM cte
WHERE rownum = 1
February 18, 2010 at 6:17 am
Thanks .. this is what i was looking for..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply