Professional Web Applications Themes

Limiting similar rows in SELECT statement... - Microsoft SQL / MS SQL Server

I have a select statement that can return many rows that have a single column with a duplicate value. I'd like to limit it to a maximum of two rows with the duplicate column. Example: Currently: TableA: Field1 Field2 ------- ------------ 1 value A 1 value B 1 value C 2 value D 3 value E What I would like is the following: Field1 Field2 ------- ------------ 1 value A 1 value B 2 value D 3 value E So the query should eliminate rows if Field 1 has more already contains two of the same value. In the example, ...

  1. #1

    Default Limiting similar rows in SELECT statement...

    I have a select statement that can return many rows that have a single
    column with a duplicate value. I'd like to limit it to a maximum of two
    rows with the duplicate column. Example:

    Currently:
    TableA:

    Field1 Field2
    ------- ------------
    1 value A
    1 value B
    1 value C
    2 value D
    3 value E

    What I would like is the following:

    Field1 Field2
    ------- ------------
    1 value A
    1 value B
    2 value D
    3 value E

    So the query should eliminate rows if Field 1 has more already contains two
    of the same value. In the example, row 3 was removed along with it's value
    C.

    Can this be done?


    Greg Guest

  2. #2

    Default Re: Limiting similar rows in SELECT statement...

    Outside of a god-awful union query:
    select top 2 field1,field2 from tbl where field1=1
    union all
    select top 2 field1,field2 from tbl where field1=2
    union all
    select top 2 field1,field2 from tbl where field1=3

    I'm not aware of any way to do this in a single query. If you had another
    field that could be used to order the rows with no ties, like this:
    Field1 Field2 Field3
    ------- ---------- ----------
    1 value A 1
    1 value B 2
    1 value C 3
    2 value D 4
    3 value E 5

    You could do something like this:

    select field1,field2 from
    (
    select field1,field2,
    (select count(*) from tbl where field1=t1.field1
    and field3<=t1.field3) recordcount
    from tbl t1
    ) t2
    where recordcount <= 2

    HTH,
    Bob Barrows

    Greg wrote:
    > I have a select statement that can return many rows that have a single
    > column with a duplicate value. I'd like to limit it to a maximum of
    > two rows with the duplicate column. Example:
    >
    > Currently:
    > TableA:
    >
    > Field1 Field2
    > ------- ------------
    > 1 value A
    > 1 value B
    > 1 value C
    > 2 value D
    > 3 value E
    >
    > What I would like is the following:
    >
    > Field1 Field2
    > ------- ------------
    > 1 value A
    > 1 value B
    > 2 value D
    > 3 value E
    >
    > So the query should eliminate rows if Field 1 has more already
    > contains two of the same value. In the example, row 3 was removed
    > along with it's value C.
    >
    > Can this be done?


    Bob Barrows Guest

Similar Threads

  1. Replies: 3
    Last Post: September 5th, 04:16 PM
  2. limiting rows returned in a sql select
    By Lennart Jonsson in forum IBM DB2
    Replies: 2
    Last Post: August 26th, 03:41 AM
  3. Is there an application similar to Info Select?
    By matt neuburg in forum Mac Applications & Software
    Replies: 4
    Last Post: July 25th, 07:59 PM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM
  5. Insert 100 rows in just one statement
    By David Portas in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 1st, 12:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139