SELECT DISTINCT OR GROUP BY?

TKS But tried that and that gives me a whole load of duplicates

bascially i need the result to be
AA1  M
AA2 M
AA3 M


tks.

which value from the size column should you pick?  random?

select distinct ref,
(select top 1 size from products p2 where p1.ref = p2.ref) as size
from products p1
WHERE (productname like '%xxx%' and  unitsinstock > '0' and [productname] like '%" & attribcolor & "%' AND [productname] like '%" & attribfabric & "%')"

REF | SIZE
AA1  M
AA1 L
AA1 XL
AA2 M
AA2 XL
AA3 M
AA3 M

those values are all distinct.  when you use that keyword, it compares all values being returned.  you need to first get a list of the unique product ref's, and then get a size for each one.

Okay I see I think i need to build 2 querys first.

The first one should be something like this

SELECT * FROM Products WHERE (productname like '%xxx%' and  unitsinstock > '0') GROUB BY [REFRENCE]"

However I am unable to groub by refrence, its gives me a Syntax error (missing operator) in query expression.

But anyway once i have a group of distinct refrences then i can do a query based on the above can't it?

strsql = "SELECT DISTINCT REFRENCE FROM Products WHERE (productname like '%cdrom%' and  unitsinstock > '0')"

So the above select all the fields REFRENCE from the products table that are distint right?

Now I need to use this query in another query i.e.

strsql2 = "SELECT * FROM STRSQL ...... and then somehow i think use inner  join or something to select the other details from refrence..

so the final data will be
AA1  M
AA2 M
AA3 M

Tks.

As WMIF has already pointed out, your initial post contains all unique records:

REF | SIZE
AA1  M
AA1 L
AA1 XL
AA2 M
AA2 XL
AA3 M
AA3 M

Note here that a unique record means that the combination of "REF" and "SIZE" is unique -- not that each "REF" itself is unique.  We can clearly see that there are duplicate AA1's, AA2's, and AA3's -- but the combination of these REF's with their respective sizes are all unique.  

If your required end result looks like this:
so the final data will be
AA1  M
AA2 M
AA3 M

Then you will need to use criteria to narrow down to only "M" sized REF's.  Something like this:

SELECT * FROM Products WHERE Size='M' ORDER BY REF

This should give you :
AA1 M
AA2 M
AA3 M
....   M

Basically, one record for each "M"-sized REF in your Products table.

Hope that makes sense.  If this doesn't help, perhaps you can try explaining in a different way what exactly you need -- we may just not be getting what you're after.

Regards,
Phil / peh803

>>Okay I see I think i need to build 2 querys first.

you dont need 2 recordsets.  use the query i gave you above and it uses a subquery to bring everything into one recordset.

peh803 you know what i mean now.

I know what unique means, and i need unique refrence numbers primiarly ... the first of refrence is what i really need.

However you mentioned that i should use

SELECT * FROM Products WHERE Size='M' ORDER BY REF

However this will not give me unique REF it will only select all ref where size = m and order them thats it.

Also Not all Refrence contain size M it may contian any other sizes..

It hink my question was not phrased properly.

In access there is a function called FIRST, where by you can select first ref from products... this will display the first of each refrence thus wil not provude duplciate refrence.  So similarly i need to select the first refrence or a distcint refrence only as well as display the other data like size with that field

For example
REFRENCE  | SIZE  | PRICE  | DESCRIPTION
AA1               L          2.40       BLACK T-SHIRT
AA1              M          2.20       BLACK T-SHIRT
AA1              S          2.10        BLACK T-SHIRT
AA2              M          4.90        WHITE T-SHIRT
AA2              XL         5.10        WHITE -TSHIRT
AA3              M           2.20         PINK T-SHIRT
AA4             XS          4.20         CREAM T-SHIRT
AA4             L            6.90           CREAM T-SHIRT

After running the qry i should get the following :
AA1               L         2.40            BLACK T-SHIRT
AA2               M        4.90            WHITE T-SHIRT
AA3               M         2.20            PINK T-SHIRT
AA4               XS        4.20            CREAM T-SHIRT

So the only unique field is refrence.

TKS. FOR ALL UR HELP.

Have you tried the SQL Statement provided my WMIF?  It seems that his statement will do what you're after.

If not, tell us what's happening when you run that statement, and how it differs from what you want.

Thanks,
Phil / peh803

What exactly are you trying to accomplish?  I've got a DB with 180K skus for apparel, 1000 colors, 130 sizes, and 1800 products.  :)  I've probably already solved this headache and can shed some light on how I did it.  Apparel is a frustrating animal because you usually need to do 3 point joins.  It's often very helpful to create matrix tables and sometimes you need to do temp tables to get what you want.  At any rate how you dice it depends on how often you have to do it.

In essence you should be dealing with some combination of style, color, size which should have sub-values of price, location, margin etc.

I'm assuming the ref is your style code.

Cursors work particularly well for this type of thing where you need to process the data intra query and don't want to make a second table.  They are hideous on performance so you may want to "cache" the data to a temp table.

It looks an awful lot like a catalog selection.  In this case you may want to pull just one size code and pick either your high or low price.  If on the other hand you are doing warehouse selection for sourcing, that usually is best done with a sku as the join and using a function to do your dirty work.

So you do something clever like

Select color, size, price, dbo.myfunction(description ) from bigtable  

usually description would be your productcode/style

then your function goes and does something like

declare myfunction (blah)
{insert declarative crap here to setup variables
declare @return as varchar(2555)

}
begin
select top 1 ref from bigtable where style like @@blah
end

my goal here isn't to hand you a fish, it's to teach you to fish and point you in the right direction to solve your problem.  I think a function will "break off" the tidbit of data you need.  It's fast, efficient, and provides for single-point edits which saves hair pulling 12 months later when the world spins in reverse and you have to re-code stuff.  It's also far better to do this in SQL then in ASP.  ASP tends to be slower in execution.

I re-read your question and you definately want to use a function to nitpick your data.  :)  It's not the only way, but it's certainly the quickest.

A function will crunch data and return one piece.  

It looks like you are trying to figure out what warehouse to pull from.... :)  IN which case you want to select the top row from data ordered on your select criteria and return it to some other function like a shopping cart.  Functions are very quick at this particular application.