Select X of each variation of a field 1

[Swi]

Hi,

What is the best way to select X records of a version within a database.

Ex. - SEQ, NAME, ADDR, CSZ, VERSION

I would like to pull X records of each unique version. There could be 2 versions to an infinite amount.

Thanks.

 

[Andrzejek]

Could you show a sample of your data in a table?
And expected result.

 

[dhookom]

You would need to provide some unique value to determine which X. Are you looking for random records? If so, you would need to add a random calculation into your query.

 

[Swi]

Requirement now change so I am actually going to read it from a CSV using ADO.

Ex. of CSV header - SEQ, NAME, ADDR, CSZ, VERSION

Random records would be fine. Just need to work out the query.

Set connCSV = New ADODB.Connection
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CSVDataPath & ";Extended Properties=" & Chr$(34) & "text;HDR=Yes;FMT=CSVDelimited;IMEX=1" & Chr$(34) & ";"
Set rsCSV = New ADODB.Recordset
rsCSV.Open "SELECT ???????? FROM [XXXXXX.csv]", connCSV, adOpenStatic, adLockReadOnly, adCmdText

 

[dhookom]

No sample data? No desired output? That's not much to go on. Typically I would create a calculated column that sequentially numbers records within a VERSION and then select all records with the sequence number less than or equal to X.

 

[Swi]

SEQ, NAME, ADDR, CSZ, VERSION
1,John1 Doe,1 Any St.,Anytown US 11111,A
2,John2 Doe,2 Any St.,Anytown US 22222,A
3,John3 Doe,3 Any St.,Anytown US 33333,B
4,John4 Doe,4 Any St.,Anytown US 44444,B
5,John5 Doe,5 Any St.Anytown US 55555,C

In a quick example, I would like to return the following if the user chose to have 1 record of each but they would have the option to choose how many of they would like to pull for each VERSION.
1,John1 Doe,1 Any St.,Anytown US 11111,A
3,John3 Doe,3 Any St.,Anytown US 33333,B
5,John5 Doe,5 Any St.Anytown US 55555,C

 

[Andrzejek]

Select Distinct VERSION FROM MyTable

You will get:
A
B
C

Now, loop thru the outcome from above and build this SQL:

SELECT TOP (1) * FROM MyTable Where VERSION = 'A'
UNION ALL
SELECT TOP (1) * FROM MyTable Where VERSION = 'B'
UNION ALL
SELECT TOP (1) * FROM MyTable Where VERSION = 'C'

Run this multi-select statement and you should get what you want.

 

[Swi]

Thanks. This is very similar to the way I was thinking of trying Andrzejek. I just did not know if there was a more efficient way.

 

[rashmibala]

How about this

SELECT *
FROM (
SELECT
SEQ,
NAME,
ADDR,
CSZ,
VERSION,
(SELECT COUNT(*)
FROM TABLE_A AS innertable
WHERE innertable.version = outertable.version
AND innertable.seq <= outertable.seq
) AS row_num
FROM
TABLE_A AS outertable
) AS final
WHERE
row_num <= 3

You can set the appropriate numbers to pull and also do not need to know the different types of versions