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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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+