Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group by

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2008-10-30 : 05:32:53

Hai,
My student table structure is as follows
Id-int
name-varchar
contactnumber-varchar
city-varchar
photo-image
It allows duplicate entries.

I have to select all fields grouped on name,city,contactnumber.grouping should be done on these three fields only because it eliminates duplicate data.
i am new to this.
Please help me.
thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 06:02:53

First of all. Is there any opertunity to recode this into a normalised structure?

You shouldn't be replicating data like name, contactNumber etc.

I'm guessing that only the photo-image changes? -- you have multiple images?

If so you can do this


SELECT
    [name]
    , [contactNumber]
    , [city]
FROM
    students
GROUP BY
    [name]
    , [contactNumber]
    , [city]


SELECT DISTINCT
    [name]
    , [contactNumber]
    , [city]
FROM
    students

Regards,

NB: just read your requirements again. You need all the fields. In that case you are going to have to decide which 1 of the non grouped by items you want. (the max, min, whatever)

Something like


SELECT
MAX(Id)
, name
, contactnumber
, city
MAX(photo-image)
FROM
    students
GROUP BY
, name
, contactnumber
, city

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 06:11:54

Or, if you actually want the latest entry for each student (ordered by Id] you could do this


SELECT
	s.[Id] As [Latest Id]
	, s.[name] AS [Student Name]
	, s.[contactNumber] AS [Contract Number]
	, s.[city] AS [City]
	, s.[photo] AS [Latest Photo]
FROM
	students s

	JOIN (
		SELECT
			MAX([Id]) AS [studentId]
		FROM
			students
		GROUP BY
			[name]
			, [contactNumber]
			, [city]
		)
		latest_student ls ON ls.[studentId] = s.[Id]

-------------
Charlie

cool.mugil
Starting Member

32 Posts

Posted - 2008-10-30 : 07:17:28

hai,
thanks.your first one solved my problem.but if i use max(photo) it throws error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources