CODEWITHSUNDEEP
sqlmysql
JD Isaacks
JD Isaacks

Reputation: 57974

MySql: Set limit for duplicate column

OK I have a column on my table called style_number. There can be duplicates of style_number. I want to select everything, but only up to 3 for each style number.

So for example if there are 4 rows that have a style_number = "style 7" only 3 of those 4 would be selected (doesn't matter which 3, any 3).

Does this make sense?

How can I do that?

Thanks!!

Note: I am using 4.1.22

EDIT: To clear some confusion. I need all the other columns as well, and I need more than just "style 7" or I could easily limit to 3. but for example if this where my table:

style_number | price | stone_count

"style 7" | 300 | 2
"style 7" | 400 | 3
"style 7" | 500 | 4
"style 7" | 600 | 5
"style 8" | 200| 1
"style 8" | 300 | 2

I would get this as a result:

"style 7" | 300 | 2
"style 7" | 400 | 3
"style 7" | 500 | 4
"style 8" | 200| 1
"style 8" | 300 | 2

Does that make better sense?

Upvotes: 2

Views: 3161

Answers (5)

wuputah
wuputah

Reputation: 11395

Because of your version of MySQL, your options may be quite limited; it may be impossible to do this in a single query, so I figured I'd give an alternative.

A simple solution would be to iterate over each style and query that style with a LIMIT 3 parameter, but you would have to execute a lot of queries. To combine these into a single query, first get a list of all the styles:

SELECT DISTINCT style_number FROM styles;

Then you can then iterate over the style_numbers to build the SQL string in your programming language so that it reads as follows:

SELECT * FROM styles WHERE style_number = 'style 7' LIMIT 3
UNION
SELECT * FROM styles WHERE style_number = 'style 8' LIMIT 3
UNION
...

Hope this helps.

Upvotes: 0

Tom H
Tom H

Reputation: 47464

I'm not a MySQL developer, but I think that this will give you what you want. No promises on performance though :)

SELECT
     T1.id,
     T1.style_number,
     T1.col1,
     T1.col2,
     ...
FROM
     My_Table T1
WHERE
     (
          SELECT
               COUNT(*)
          FROM
               My_Table T2
          WHERE
               T2.style_number = T1.style_number AND
               T2.id < T1.id
     ) < 3

Upvotes: 1

z-index
z-index

Reputation: 389

The TOP clause allows us to specify how many rows to return.

For example: SELECT TOP 3 * FROM table WHERE style_number = "style 7"

http://www.w3schools.com/sql/sql_top.asp

Upvotes: -1

Mike Cialowicz
Mike Cialowicz

Reputation: 10020

Take a look at the LIMIT syntax here.

Upvotes: 0

Ben S
Ben S

Reputation: 69342

SELECT * FROM yourTable WHERE style_number = "style 7" LIMIT 3

This will limit the result set to a maximum of 3 rows, whichever 3 the database engine finds first. If you want the top 3 on some attribute you could add ORDER BY colName before the LIMIT 3.

Upvotes: 0

Related Questions