Dublicate records in query result 
Author Message
 Dublicate records in query result

 Hello!

 I have a simple table with some data, and one of fields has a "int" type
  i.e.

    Caption  Q
      'a'          1
      'b'          3
      'c'           2

 I would like to get as many dublicated records, as a value in "Q" field

   a
   b
   b
   b
   c
   c

Is it possible?

(without additional table with data like 1, 2,2, 3,3,3, 4,4,4,4 etc)

 with best regards,
 Ilya Andreev AKA Andre
 DelphiCity Team
 www.delphicity.net



Fri, 04 Apr 2003 03:00:00 GMT
 Dublicate records in query result

    Have a table of numbers. Then you can do:

SELECT t1.Caption
FROM tbl AS t1
CROSS JOIN Numbers AS n
WHERE n.Number <= t1.Q

-- Or, for smaller values of "Q"

SELECT t1.Caption
FROM tbl AS t1
CROSS JOIN ( SELECT 1 UNION ALL SELECT 2 UNION ALL
             SELECT 3 UNION ALL SELECT 4 UNION ALL
             SELECT 5 UNION ALL SELECT 6 UNION ALL
             SELECT 7 UNION ALL SELECT 8 UNION ALL
             SELECT 9
) AS n( Number )
WHERE n.Number <= t1.Q

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)



Fri, 04 Apr 2003 03:00:00 GMT
 Dublicate records in query result

 Hello!

 I said - no additional table with numbers. Could you read?

 with best regards,
 Ilya Andreev AKA Andre
 DelphiCity Team
 www.delphicity.net


Quote:
>     Have a table of numbers. Then you can do:

> SELECT t1.Caption
> FROM tbl AS t1
> CROSS JOIN Numbers AS n
> WHERE n.Number <= t1.Q

> -- Or, for smaller values of "Q"

> SELECT t1.Caption
> FROM tbl AS t1
> CROSS JOIN ( SELECT 1 UNION ALL SELECT 2 UNION ALL
>              SELECT 3 UNION ALL SELECT 4 UNION ALL
>              SELECT 5 UNION ALL SELECT 6 UNION ALL
>              SELECT 7 UNION ALL SELECT 8 UNION ALL
>              SELECT 9
> ) AS n( Number )
> WHERE n.Number <= t1.Q

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> (Please reply only to the newsgroup)



Sat, 05 Apr 2003 03:00:00 GMT
 Dublicate records in query result

No



Quote:
> Hello!

>  I have a simple table with some data, and one of fields has a "int" type
>   i.e.

>     Caption  Q
>       'a'          1
>       'b'          3
>       'c'           2

>  I would like to get as many dublicated records, as a value in "Q" field

>    a
>    b
>    b
>    b
>    c
>    c

> Is it possible?

> (without additional table with data like 1, 2,2, 3,3,3, 4,4,4,4 etc)

>  with best regards,
>  Ilya Andreev AKA Andre
>  DelphiCity Team
>  www.delphicity.net



Sat, 05 Apr 2003 03:00:00 GMT
 Dublicate records in query result

Quote:
>> I said - no additional table with numbers. Could you read?

    Can't you? I proposed 2 different methods. Why don't you use the other
SELECT statement & build a virtual table till the maximum number you want?
Or write a logic in a SP - I don't know of any.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)



Sat, 05 Apr 2003 03:00:00 GMT
 Dublicate records in query result

Hi,

I would also have suggested the use of a driver table, much easier (just a
table with integers from 0 to n-1, easily filled)... and it seems to be
definitively the way to go. You say no table like 1, 2, 2, 3, 3, 3, 4, 4, 4,
4, .... and it is not, since it is just a table like 0, 1, 2, 3, 4, 5, 6,7,
8, 9, ... a table that no database has trouble to keep.

Anyhow, if your initial table has at least as many records that the maximum
quantity  of duplicaton of the same record, slow but doable (using the table
itself as a pseudo driver table, here the table b from which we pick no
field at all):

SELECT     a.F1, a.Qty
FROM         dbo.Table1 a CROSS JOIN
                      dbo.Table1 b
WHERE     (a.Qty >=
                          (SELECT     COUNT(*)
                            FROM          Table1
                            WHERE      pk >= b.pk))
ORDER BY a.F1, a.Qty

Hoping it may help,
Vanderghast, Access MVP



Quote:
> Hello!

>  I said - no additional table with numbers. Could you read?

>  with best regards,
>  Ilya Andreev AKA Andre
>  DelphiCity Team
>  www.delphicity.net



> >     Have a table of numbers. Then you can do:

> > SELECT t1.Caption
> > FROM tbl AS t1
> > CROSS JOIN Numbers AS n
> > WHERE n.Number <= t1.Q

> > -- Or, for smaller values of "Q"

> > SELECT t1.Caption
> > FROM tbl AS t1
> > CROSS JOIN ( SELECT 1 UNION ALL SELECT 2 UNION ALL
> >              SELECT 3 UNION ALL SELECT 4 UNION ALL
> >              SELECT 5 UNION ALL SELECT 6 UNION ALL
> >              SELECT 7 UNION ALL SELECT 8 UNION ALL
> >              SELECT 9
> > ) AS n( Number )
> > WHERE n.Number <= t1.Q

> > --
> > Umachandar Jayachandran
> > SQL Resources at http://www.umachandar.com/resources.htm
> > (Please reply only to the newsgroup)



Sat, 05 Apr 2003 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Removing dublicate records in table and hostarray

2. MDX error: dublicate dimensions across (independent) axes: when calculating a query axis

3. How can SP return records from the last query results

4. #deleted appears in resulting query for some records

5. retrieve the first 100 records from the query result

6. Query results over 200 000 Records ?!?!!??!

7. way to keep the results in query analyzer results pane across multiple runs

8. [Querying A Subset of Results in a Result Set]

9. way to keep the results in query analyser results pane across multiple runs

10. Dublicate key

11. running a query on the result set of another query

12. dublicate key insertion


 
Powered by phpBB® Forum Software