January 25, 2003 at 2:15 pm
Whats the criteria you're trying to apply?
Andy
January 25, 2003 at 4:36 pm
quote:
Whats the criteria you're trying to apply?Andy
http://www.sqlservercentral.com/columnists/awarren/
I'm new to SQL - please forgive my confusion.
By criteria, I'm assuming we're talking about anything after a "WHERE" clause.
There are many barcodes and a mode must come from the multiple measurements for each of the barcodes, as indicated.
Looking at my example, there are two barcodes in the table - 120 and 123. Barcode # 120 has 2 datapoints (measurement1 and measurement2). There are 3 samples with data from barcode #120. Each sample measurement for a barcode must be resolved into one measurement by taking their mode.
Edited by - jcstubborn2001 on 01/25/2003 4:41:37 PM
January 25, 2003 at 6:23 pm
Maybe its just me, but what is 'taking their mode'?
Andy
January 25, 2003 at 6:33 pm
quote:
Maybe its just me, but what is 'taking their mode'?Andy
http://www.sqlservercentral.com/columnists/awarren/
I'm sorry for the confusion.
The mode would be the number with the highest frequency. So, for the values for barcode #120 in the measurement1 column, there are three values: 45, 60 and 60. So, in this case, 60 is the mode.
Edited by - jcstubborn2001 on 01/25/2003 6:33:56 PM
January 26, 2003 at 4:58 am
Then should't your data be returning the other row as 123, 70 50? This is not a great TSQL solution, but might give you ideas:
create table #temp (Rowid int, Barcode int, M1 int, M2 int)
create unique index ndxbarcode on #temp (barcode) with ignore_dup_key
insert into #temp (rowid, barcode, m1, m2)
select rowid, barcode, m1, m2 from test order by barcode asc, m1 desc
select * from #temp
drop table #temp
Andy
January 26, 2003 at 4:54 pm
This is the base code I use to determine mode.
SELECT [id] AS MODE FROM tblHA GROUP BY [id] HAVING COUNT(*) = (
SELECT MAX(CNT) AS MaxCNT FROM (
SELECT count(*) AS CNT FROM tblHa GROUP BY [id]) AS base)
Adjust for your needs.
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply