Flat File Connection with different row types

I have a file format that uses many rows to describe relationships between one-to-many entries. Basically in many tables I one file with the only relations between tables been the order of the rows.

From what I’ve found so far, the Flat file Connection is not the correct Connection type to use for this. Is there something better suited, or documentation of creating Connection types.

Cheers Simeon.

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamiesql

First 5 of a each group

I have a field that has different entries. Is there a way I can pull the first 5 of each one?

I can get the count of each different one, I just don't see how to get the first 5 of each.

SELECT PostType, COUNT(*) AS NumberOfEntires
FROM ECNADetail GROUP BY PostType ORDER BY PostType

That returns

PostType

NumberOfEntires

1

4924

2

181

3

3621

4

695

How many distinct post types are there? You could do:

Select Top 5 * from ECNADetail Where PostType=1

Union

Select Top 5 * from ECNADetail Where PostType=2

Union

....

|||

Hi Tealc,

Try this script:

select * from ECNADetail as t
where (select count(*) from ECNADetail where PostType=t.PostType and entries>t.entries)<5 order by PostType,entries desc

|||

Your definition of first 5 entries of PostType is not clear.

The following solution is based a datetime column (if you have one)

For Sql Server 2005, you can try:

SELECT
*FROM(SELECT*, Row_Number()OVER(PartitionBy PostTypeOrderBy PostType, YourdatetimeColumnDESC)as seq
FROM
ECNADetail)t1
WHERE
 t1.seq<6
 
If you don't have the datetimecolumn, you can choose other column to decide the order to choose the top 5 for each group.

|||I ended up doing the union with each type selected as the top 5. This was a one time run thing.