is there a way to set a limit by group?

[LaureenJ]

I'm trying to adjust a query but I am very new to using SQL and just found this forum. I would appreciate any help. This will make me much more efficient at my job.

Here is a mock up:

SELECT item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute IN ('attribute1','attribute2','attribute3')
ORDER BY item_attribute

This returns 50 of attribute 1, 30 of attribute 2 and 15 of attribute 3. I want to limit each of them to 20.

Basically I'm after a sampling of each of those attributes without have to run 3 separate queries and copying and pasting no more than 20 of each.

Is there a way to accomplish this?

Thank you!

--Laureen

 

[Juice05]

Try the "Top" Function to return a set amount of records.

You can also use that with a "Union" to join several queries together.

 

[bluecjh]

SO LIKE... AS Juice05 says

SELECT top 50 item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute ='attribute1'
ORDER BY item_attribute

UNION

SELECT top 30 item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute = 'attribute2'
ORDER BY item_attribute

UNION

SELECT top 15 item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute = 'attribute3'
ORDER BY item_attribute

BlueCJH

 

[r937]

problem is, TOP isn't ANSI SQL

r937.com | rudy.ca

 

[bluecjh]

might be yours but not Lauren's

BlueCJH

 

[r937]

no, blue, i don't think you get it

this is the ANSI SQL forum

furthermore, we don't know what database system lauren is using -- we can guess, but we don't know

many people post here who should really be posting in some other forum more specific to their particular dialect of SQL, but that is not enough reason for us to provide solutions which might work but might just as easily not work

we should always provide ANSI SQL solutions in this forum regardless of whether the person who originally posted the question knows what ANSI SQL is or not

r937.com | rudy.ca

 

[bluecjh]

Ugh! How patronising, good luck Lauren.

BlueCJH

 

[LaureenJ]

Thank you Juice05 and especially you blueCGH for spelling it out. I should have mentioned what database I'm using - it is mySQL. Does the TOP command work with that database type? If this is the wrong forum please point me to the correct one b/c I plan on having more questions!

--Laureen = )

 

[r937]

no, TOP does not work in MySQL

you want forum436

r937.com | rudy.ca

 

[Juice05]

I think LIMIT can be used in place of TOP in MySQL.

 

[LaureenJ]

So like this?:

SELECT item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute ='attribute1'
ORDER BY item_attribute
LIMIT 10

UNION

SELECT item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute = 'attribute2'
ORDER BY item_attribute
LIMIT 10

UNION

SELECT item_id
FROM item_changes
WHERE customer = 'ABC'
AND item_timestamp >= '2006-01-08'
AND item_timestamp < '2006-01-14'
AND item_attribute = 'attribute3'
ORDER BY item_attribute
LIMIT 10

 

[r937]

if it works, then yes, just like that

however, the mysql manual suggests parenthesizing the subselects --

( select ... limit 10 )
union all
( select ... limit 10 )
union all
( select ... limit 10 )

note i used UNION ALL instead of UNION in order to avoid the unnecessary sort for duplicates (since there can't be any)

if you need further assistance, may i suggest starting a new thread in the mysql forum


r937.com | rudy.ca