Select 'First', Count and Having Expressions
I am trying to convert the following MS Access Query to T-SQL and the solution eludes me.
SELECT First([The_Raw_Data].[ICN]
INTO [_DB_No_Line_1_ICNs]
FROM [The_Raw_Data]
GROUP BY [The_Raw_Data].[ICN]
HAVING (((Count([The_Raw_Data].[I
ORDER BY [The_Raw_Data].[ICN], Min(CInt([DTL_NUM]));
It works in Access and I already know what the correct result is; I have not been able to replicate that result in SQL. Here is one of my many iterations ...
SELECT [ICN], [DTL_NUM] INTO [DB_No_Line_1_ICNs]
FROM [Raw_Data]
GROUP BY [ICN], [DTL_NUM]
HAVING COUNT([ICN])>=1 AND MIN(CAST([DTL_NUM] AS INTEGER))>1
ORDER BY [ICN] ASC;
Here is some sample data ...
ICN DTL_NUM
2311018009815 2
2311018009815 3
2311018009815 4
2311025005375 5
2311025005375 6
2311237000712 1
2311237000712 2
2312144002972 2
2312144002972 3
2312144002999 2
2312144002999 4
2312185136028 1
2312185136028 2
2312185136028 3
... and the Result should be ...
2311018009815 2
2312144002972 2
2312144002999 2
SELECT First([The_Raw_Data].[ICN]
INTO [_DB_No_Line_1_ICNs]
FROM [The_Raw_Data]
GROUP BY [The_Raw_Data].[ICN]
HAVING (((Count([The_Raw_Data].[I
ORDER BY [The_Raw_Data].[ICN], Min(CInt([DTL_NUM]));
It works in Access and I already know what the correct result is; I have not been able to replicate that result in SQL. Here is one of my many iterations ...
SELECT [ICN], [DTL_NUM] INTO [DB_No_Line_1_ICNs]
FROM [Raw_Data]
GROUP BY [ICN], [DTL_NUM]
HAVING COUNT([ICN])>=1 AND MIN(CAST([DTL_NUM] AS INTEGER))>1
ORDER BY [ICN] ASC;
Here is some sample data ...
ICN DTL_NUM
2311018009815 2
2311018009815 3
2311018009815 4
2311025005375 5
2311025005375 6
2311237000712 1
2311237000712 2
2312144002972 2
2312144002972 3
2312144002999 2
2312144002999 4
2312185136028 1
2312185136028 2
2312185136028 3
... and the Result should be ...
2311018009815 2
2312144002972 2
2312144002999 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Here is some sample data ...
ICN DTL_NUM
2311018009815 2
2311018009815 3
2311018009815 4
2311025005375 5
2311025005375 6
2311237000712 1
2311237000712 2
2312144002972 2
2312144002972 3
2312144002999 2
2312144002999 4
2312185136028 1
2312185136028 2
2312185136028 3
... and the Result should be ...
2311018009815 2
2312144002972 2
2312144002999 2 <<
Can you explain in plain terms the criteria used to get those desired results?
ICN DTL_NUM
2311018009815 2
2311018009815 3
2311018009815 4
2311025005375 5
2311025005375 6
2311237000712 1
2311237000712 2
2312144002972 2
2312144002972 3
2312144002999 2
2312144002999 4
2312185136028 1
2312185136028 2
2312185136028 3
... and the Result should be ...
2311018009815 2
2312144002972 2
2312144002999 2 <<
Can you explain in plain terms the criteria used to get those desired results?
ASKER
I apologize for the delay, been swamped around here ...
PatHartman ...
I believe that count(*) was in one of my iterations; however I went through so many variations I do not remember the results. I will have to try it again.
ScottPletcher ...
I will testing your query later today.
awking00 ...
The Access Query that I posted produces those results; each ICN must have a #1 line or it cannot be processed by the application; as result I need a way to identify the "No Line 1" ICN's and pull all of those related data rows out, so the remaining data set can be processed. My typical data set has upwards of a half-million data rows and Access does OK processing that or less; however the data sets are getting larger. The current data set has 3.1 million data rows, which is to much for Access to handle.
PatHartman ...
I believe that count(*) was in one of my iterations; however I went through so many variations I do not remember the results. I will have to try it again.
ScottPletcher ...
I will testing your query later today.
awking00 ...
The Access Query that I posted produces those results; each ICN must have a #1 line or it cannot be processed by the application; as result I need a way to identify the "No Line 1" ICN's and pull all of those related data rows out, so the remaining data set can be processed. My typical data set has upwards of a half-million data rows and Access does OK processing that or less; however the data sets are getting larger. The current data set has 3.1 million data rows, which is to much for Access to handle.
ASKER
Have not forgot; other projects moved ahead of this one.
>>each ICN must have a #1 line or it cannot be processed by the application; as result I need a way to identify the "No Line 1" ICN's <<
Then why is icn 2311025005375 not in your expected results as it only has dtl_num of 5 and 6?
Then why is icn 2311025005375 not in your expected results as it only has dtl_num of 5 and 6?
ASKER
awking00,
I actually typed those in, not a cut paste and it does show in the query results.
I actually typed those in, not a cut paste and it does show in the query results.
So the following record should show in the final results?
icn dtl_num
2311025005375 5
If so,
select icn, dtl_num from
(select icn, dtl_num, row_number() over (parttion by icn order by dtl_num) as rn
from yourtable) x
where x.rn = 1 and x.rn < x.dtl_num;
icn dtl_num
2311025005375 5
If so,
select icn, dtl_num from
(select icn, dtl_num, row_number() over (parttion by icn order by dtl_num) as rn
from yourtable) x
where x.rn = 1 and x.rn < x.dtl_num;
ASKER
Have not forgot ...
ASKER
ScottPletcher,
BINGO! :)
BINGO! :)
The only change I would make is to use Count(*) rather than Count(somefieldname). The former counts all the rows in the set. The latter counts all the rows in the set but ONLY if the specified field is not null. Also, the count(*) is more efficient than the Count(somefieldname) since it can frequently be obtained directly from the table statistics whereas counting a specific column name requires that each row be examined one at a time.