Have you redundant records? You select a lot of fields, and you showed us only 3... If all other fields you selected in the query and NOT showed here are identical too (so fields in the two rows are completely equals) you can try:
.Open "SELECT DISTINCT [Prestasi hasil].Negeri,[Prestasi hasil].[Lokasi Projek],[Prestasi hasil].Musim,pemilik.Pemilik1,pemilik.Pemilik2,[Prestasi hasil].Plot,[Prestasi hasil].[Hasil Kasar (kg)],[Prestasi hasil].[Purata Hasil Kasar (Tan/ha)],[Prestasi hasil].[Hasil Bersih (-17 %)],[Prestasi hasil].[Purata Hasil Bersih (Tan/ha)],[Prestasi hasil].Masalah FROM [Prestasi hasil],pemilik where [Prestasi hasil].Plot=pemilik.Plot GROUP BY [Prestasi hasil].Negeri,[Prestasi hasil].[Lokasi Projek],[Prestasi hasil].Musim,pemilik.Pemilik1,pemilik.Pemilik2,[Prestasi hasil].Plot,[Prestasi hasil].[Hasil Kasar (kg)],[Prestasi hasil].[Purata Hasil Kasar (Tan/ha)],[Prestasi hasil].[Hasil Bersih (-17 %)],[Prestasi hasil].[Purata Hasil Bersih (Tan/ha)],[Prestasi hasil].Masalah "
Have you redundant records? You select a lot of fields, and you showed us only 3... If all other fields you selected in the query and NOT showed here are identical too (so fields in the two rows are completely equals) you can try:
While correct (w/si's caveat), if all fields in the records are identical, you need to fix your db design. There should never be identical records in a table. If you don't want duplicate values in a field or group of fields, consider making them into a primary key. This will prevent duplicate entries. Si's method will allow you to check first, and give the user an error message when entering data.
If the selected fields can contain duplicates (i.e. all fields in the record, including those you haven't selected, aren't duplicates), use the DISTINCT or GROUP BY clauses. One of the gurus here once posted that GROUP BY is better, but can't recall why.
In some database engines DISTINCT is performed after the "working resultset" is completely built.
GROUP BY only inserts rows inthe the "working resultset" if they do not already appear. And of course GROUP BY processes any aggregate functions at the same time.
With MS SQL 2000 this difference went away - DISTINCT now uses the more powerful GROUP BY logic.
I would imagine that not all DB engines do this the same way.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
I just want to know where I need to put the DISTINCT if the SQL command like this because the sample they give stated the field name in the SQL command
I don't like DISTINCT. I want to always know why it's being used - as it usually is never needed.
Although - I just used it myself in a STORED PROCEDURE I'm writing for a web-page
Code:
Select Distinct SC.ConfData
,BU.BldgName
,TM.TeamDescr
,RM.Team
,RM.Bldg
,RM.Yr
,@RemoteUser
From Building_T BU
Left Join Room_T RM on RM.Yr=@Yr and RM.Bldg=BU.Bldg
Left Join Stufiles_T SC on SC.ConfItem='DistrictName'
Left Join Team_T TM on TM.Yr=RM.Yr and TM.Bldg=RM.Bldg and TM.Team=RM.Team
Where BU.Bldg=@Bldg
Order by RM.Bldg,RM.Team
I did this because there are dozens and dozens of rooms for a building in the ROOM_T table. But all I want is the "distinct" list of TEAM fields from that table (there might be only 3 or 4 different teams in a school building).
So I understand that my JOIN to ROOM_T is "weak" - but it's intentional because of what my end goal is - to get the DISTINCT TEAMS from that ROOM table.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Xmas79 - why did you suggest DISTINCT and GROUP BY in the same query?
They bsically produce the same output - right?
They produce the same output, but they can procude different execution plans, depending on the engine and the query itself (ok, not the query above). But sorry for putting both in the same query.......
And sorry for answering late too... VBForums mail just arrived a few minutes ago...
As Salvelinus already indicated - you should never have duplicate rows in a single table.
For that matter, you should never have rows duplicated between tables, i.e. the same values in two tables.
The only exception I can see is if one of the tables has fields that can be populated by calculated values which can change over time. This would probably be better dealt with by a query, but there may be times not. Not the SQL guru myself.
[RESOLVED] redundant record
Reply With Quote
Originally Posted by Xmas79
.... 