update few rows select by group by clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi Expert,

Thanks for your attention.

I have this.

SQL

ID Student Class
1  Raj     5
2  Ram     1
3  Ratan   4
4  Raj     6
5  Dijen   3
6  Ratan   7
7  Ritesh  12

Here ID is identity number increasing 1

First occurence of any Student Record.These class data replace with 1.
After updation I want

SQL

ID Student Class
1  Raj     1
2  Ram     1
3  Ratan   1
4  Raj     6
5  Dijen   1
6  Ratan   7
7  Ritesh  1

Thanks in advance for your solution.

Posted 4-Sep-12 23:37pm

Kaushik Saha from Kolkata,India

Add a Solution

Comments

manognya kota 5-Sep-12 5:52am

Could you please explain your requirement clearly. Why do you require this?

VIPR@T 5-Sep-12 5:54am

Can you please explain in brief? not getting your requirement.

Malli_S 5-Sep-12 5:58am

I wonder you may have to write stored procedure (provided you are using SQL :P )
BTW which database you're using ?

3 solutions

Solution 3

Here is a sample approach

SQL

CREATE TABLE #Students (ID  INT IDENTITY(1,1), Student VARCHAR(50), Class INT)

INSERT INTO #Students
SELECT 'Raj', 5 UNION ALL
SELECT 'Ram', 1 UNION ALL
SELECT 'Ratan', 4 UNION ALL
SELECT 'Raj', 6  UNION ALL
SELECT 'Dijen', 3 UNION ALL
SELECT 'Ratan', 7 UNION ALL
SELECT 'Ritesh', 12


SELECT * FROM #Students


UPDATE #Students
SET Class = 1
WHERE ID IN
(SELECT MIN(ID) AS ID FROM #Students GROUP BY Student)


SELECT * FROM #Students


DROP TABLE #Students

Posted 5-Sep-12 0:06am

__TR__

Comments

Mohamed Mitwalli 5-Sep-12 6:54am

5+

manognya kota 5-Sep-12 7:27am

5'ed

Solution 1

Hi,

As per my understanding, you want to update all the first occurances to 1 in the class column.
If so, refer below link.Also, you need to use a order by clause in your statement to get this work.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102391[^]

Hope this helps.

Posted 5-Sep-12 0:02am

manognya kota

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ssd_coolguy · Accepted Answer · 2012-09-05T00:06:00

Solution 2

try below query:-

SQL

update tableA
set class ='1'
where id in (select min(id)
             from tabelA 
             group by name)

Posted 5-Sep-12 0:06am

ssd_coolguy

Comments

manognya kota 5-Sep-12 6:22am

5'ed.

ssd_coolguy 5-Sep-12 6:25am

Thanks.. :)

Mohamed Mitwalli 5-Sep-12 6:53am

5+

update few rows select by group by clause

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

update few rows select by group by clause

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us