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 |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-01-14 : 07:58:04
|
| Hi i've a strange request, I need to report on unique records from a table that has duplicates.the users only want to see one example.here is my data.col1 col2 col3 col4a 123 12345 xxxa 123 12345 xxy a 123 12345 xvvif i were to group these i get 3 rows, but in my case i only need to see one row and as you can it's col4 the description that not unique so this make it display 3 rows.Any ideas on how i can get this just to display one row of data. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 08:24:02
|
| [code]SELECT t.*FROM table tINNER JOIN (SELECT col1,col2,col3,MAX(col4) AS col4 FROM table GROUP BY col1,col2,col3)tmpON tmp.col1 =t.col1 AND tmp.col2=t.col2 AND tmp.col3=t.col3AND tmp.col4=t.col4[/code] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-14 : 08:29:08
|
No need to JOIN here if there are only four columns for presenationSELECT col1, col2, col3, MAX(col4) AS col4FROM table1GROUP BY col1, col2, col3
E 12°55'05.63"N 56°04'39.26" |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-01-14 : 09:33:59
|
quote:
Originally posted by Peso
E 12°55'05.63"N 56°04'39.26"
Peso,I noticed your coordinated places you somewhere in the Arabian Sea. Are you in the Navy???
Avoid Sears Home Improvement |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-14 : 10:05:08
|
56 degrees North, 12 degrees east.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-01-14 : 10:15:44
|
Ah! Europe; over by Copenhagen. I think I spent *way* too much time playing with this already. Completely nonproductive at work so far, but an interesting distraction.
Avoid Sears Home Improvement |
|
|
|
|
|
|
|
|
Getting unique records