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.
| Author |
Topic |
|
Stéphane
Starting Member
1 Post |
 Posted - 2012-08-20 : 04:02:37
|
| I have the folowing table trjtodo with folowing columnsIDColorName there are several rows where the same NAME occurs. I want the ID and color from the all the rows with first occurance of a NAMEex.1 | Blue | John2 | Red | Sam3 | Yellow | John4 | Red | Soniawhat I need is an SQL statement that returns1 | blue | John2 | Red | Sam4 | Red | SoniaI tried : Select id, color, min(name) name from trjtodo group by 1,2but it returns me the 4 rows.thanks for helping me out here !Regards, |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-20 : 04:24:51
|
| [code]create table dbo.test (ID int not null identity(1, 1), colour varchar(20), Name varchar(20)) insert into dbo.test (colour, name)select 'Blue', 'John' union allselect 'Red', 'Sam' union allselect 'Yellow', 'John' union allselect 'Red', 'Sonia'select a.ID, a.Colour, a.Name from (SELECT ID, Colour, Name,RANK() OVER (PARTITION BY Name order by ID) as rankingFROM dbo.test) as awhere a.ranking < 2drop table dbo.test[/code] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-08-20 : 05:21:37
|
You can't reference columns with ordinal positions when doing GROUP BY.Spell out the column names and you should be fine.group by 1,2group by colour, name
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
SQL Statement