MSSQL Group By problems

[zelwin]

I am having an SQL problem.

Example Data;
name shop price description
XXXX 247elec 1000 a
XXXX argos 1200 aa
XXXX comet 1300 aaa
XXXX doitall 1400 aaaa

Basicly i want to creata a view that will give me

name shop price description
XXXX 247elec 1000 aaaa

This is
Group by name
Min of price
MAX length description
corresponding shop to min price

In reality i want to be able to GROUP BY name (coming from a join) select the MIN(price) all the corresponding columns to the MIN(price) AND The longest Description from a feed

Any help will be very much appreciated as it has had me stumped now for about a day.

[si_the_geek]

That's an awkward set of requirements, and I dont think it's possible with a Group By. You can use a sub-query to get most of it tho:

SELECT [name], shop, price, [description]
FROM tableA t1
WHERE price = (
  SELECT Min(price) 
  FROM tableA t2
  WHERE t2.[name] = t1.[name])

..this will get everything you want, except that the description will come from the record with the lowest price.

[FunkyDexter]

Not sure if I'm slightly missunderstanding what you're doing there Si but shouldn't that inner select be Select Min(Price)?

That raises a potential issue as well. What happens if there are 2 shops with the minimum price - which would you want returned? Si's query will return both as two separate lines.

[zelwin]

CHeers, Ill give it a go

[si_the_geek]

Not sure if I'm slightly missunderstanding what you're doing there Si but shouldn't that inner select be Select Min(Price)?

yep, corrected.

[zelwin]

Thank you Very much for the help guys,

This is producing Results

SELECT [myname], advertiser, searchprice, [description]
FROM dbo.FeedJOINmodelNoTable t1
WHERE searchprice = (
SELECT Min(searchprice)
FROM dbo.FeedJOINmodelNoTable t2
WHERE t2.[myname] = t1.[myname])

BUT it is not quite as i want it.
There are duplicated rows in 'myname' but with different details in description
and I am not selecting the longest description. I would also like to add a WHERE price > 0

I dont know enough about MS SQL to know how possible what i want is.

At the moment i am trying to build this database in MSSQL having built a working version in Access but it seems every step i make in the right direction i am finding 3 new problems that totaly baffle me.

Also i can see where improvments to the access version should be made but again i am having more trouble than i expected as i am a total novice at MSSQL.

[si_the_geek]

I know about the description issue, but can't think how to deal with it appropriately.

Here's the Price > 0 bit:

SELECT myname, advertiser, searchprice, [description]
FROM dbo.FeedJOINmodelNoTable t1
WHERE searchprice = (
    SELECT Min(searchprice)
    FROM dbo.FeedJOINmodelNoTable t2
    WHERE t2.myname = t1.myname
    AND searchprice > 0)

There are duplicated rows in 'myname' but with different details in description

Can you show us an example of the data? (some rows from the original table, and the output rows that relate to them).


If you have this same query working in Access, can you show us the SQL from that?

[zelwin]

In access as i said it was not ideal.

There is a join between the feed and the myproducts table where %feed.name% LIKE mytable.modelno.

This gives me a big table with all the info from mytable repeated every time there is acorresponding product in the feed.

This Join table is an "access make-table query" so (SELECT INTO) and this sorted the grouped results by price asc eg.

name shop price Description
XXX 247 100 bla bla bla
XXX emd 110 bla bla bla bla
XXX M&M 120 bla bla bla bla bla
YYY 247 100 bla bla bla
YYY emd 110 bla bla bla bla
YYY M&M 120 bla bla bla bla bla

The way i got the end result was not very good and I never managed to get the longest Description.

I did a GROUP BY myproduct Table and got MIN.price and FIRST of everything else in access, a function not supported by MSSQL.


What i am thinking of now is to create multiple Views to pull the individual things i want one at a time and then use an identifier to pull the results from how ever many views it takes back together to create the table of data i am looking for.

[szlamany]

This sub-query will get the max length item - here's a test of it:

Declare @TestTbl Table (IdVal int, Descr varchar(100))
Insert into @TestTbl Values (1,'a')
Insert into @TestTbl Values (2,'ab')
Insert into @TestTbl Values (3,'abcd')
Insert into @TestTbl Values (4,'abc')
Insert into @TestTbl Values (5,'abcde')
Insert into @TestTbl Values (6,'abcdefg')
Insert into @TestTbl Values (7,'abcdef')
select * from @testtbl where len(descr)=(select max(len(descr)) from @testtbl)

returns...

IdVal       Descr                                                                                                
----------- ---------------------------------------------------------------------------------------------------- 
6           abcdefg

(1 row(s) affected)

You probably want it to be a "SELECT TOP 1" so that it won't ever return two rows (which sub-queries don't like!).