August 12, 2008 at 2:16 am
I have this situation where in I need to produce unique asst no. from this data. I need all the fields.
Original data:
LogID Machine Code asst No. datetime Tline
1SRDT00104000146100012008-08-06 03:11:04.000 2
2SRDT00104000146100012008-08-07 03:11:04.000 4
3SRDT00114000080500312008-08-08 03:11:04.000 6 4 SRDT00094000080500322008-08-09 03:11:04.000 8
5SRDT00084000146000132008-08-10 03:11:04.000 10
when i use distinct like this
SELECT DISTINCT(Assetno),MAx(pcdatetime),tline FROM Log_Transactions
group by tline,Assetno
asstno datetime tline
4000080500312008-08-08 03:11:04.0006
4000080500322008-08-09 03:11:04.0008
4000146000132008-08-10 03:11:04.00010
4000146100012008-08-06 03:11:04.0002
4000146100012008-08-07 03:11:04.0004
the ouput is wrong because asst no. 400014610001 is still has duplicate. I need a unique output for each asst no.
the correct ouput should be like this:
asstno datetime tline
4000080500312008-08-08 03:11:04.0006
4000080500322008-08-09 03:11:04.0008
4000146000132008-08-10 03:11:04.00010
4000146100012008-08-07 03:11:04.0004
Thank You in advance!
August 12, 2008 at 3:02 am
You're getting repeated rows because you're grouping by the tline as well as the asset number. If you want to see the max tline for eachn asset number (which is what your example shows), try this.
SELECT Assetno,MAx(pcdatetime),MAX(tline) FROM Log_Transactions
group by Assetno
If that doesn't give the results you want, please explain more about what datetime and tline you want to see for the asset number
Also, distinct isn't a function. It's a keyword that applies to the entire select clause and returns distinct rows, not distinct individual columns. Brackets around column names are ignored. Your statement was interpretted by SQL as
SELECT DISTINCT Assetno,MAx(pcdatetime),tline FROM Log_Transactions
group by tline,Assetno
meaning you wanted distinct combinations of the 3 columns.
Does that make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 3:21 am
ok to make it simple.
from this data:
asstno datetime tline
400008050031 2008-08-08 03:11:04.000 6
400008050032 2008-08-09 03:11:04.000 8
400014600013 2008-08-10 03:11:04.000 10
400014610001 2008-08-06 03:11:04.000 2
400014610001 2008-08-07 03:11:04.000 4
I must have this ouput in which you will notice that this item "400014610001,2008-08-06 03:11:04.000,2" was not include because I just need the latest data base on datetime field.
asstno datetime tline
400008050031 2008-08-08 03:11:04.000 6
400008050032 2008-08-09 03:11:04.000 8
400014600013 2008-08-10 03:11:04.000 10
400014610001 2008-08-07 03:11:04.000 4
August 12, 2008 at 3:33 am
Ok, a little more challenging, but not much so. Needs a subquery.
SELECT Log_Transactions.assetno, Log_Transactions.pcdatetime, Log_Transactions.tline
FROM Log_Transactions INNER JOIN
(SELECT asstno, MAX(pcdatetime) AS LatestDate FROM Log_Transactions) MaxValues
ON Log_Transactions.asstno = MaxValues.asstno AND Log_Transactions.pcDatetime = MaxValues.LatestDate
Does that work?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 4:10 am
yes it work! thanks, actually I already did it but in not so good SQL because what I did is that I pass it to a view then use that view to add to another query to come up with the final output. I think Its better for me to use your sql. Thanks a lot!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply