DISTINCT QUERY PLEASE HELP

I do an distinct query like this:
query = "SELECT DISTINCT name, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
The query works fine but I need to select one more field called "info" so I just tried this:
query = "SELECT DISTINCT name, info, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
but then I get the error message:
"Column 'products.info' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
I want do group just by "name" and not "info"
How can I select this "info" field to?? somebody know??

Hi,
You can use
Select name, max(info), total=count........ Group by name
To "fool" the Sql server, but I am not sure this is what you want.
Will the info column have the same value for all the rows with the samevalue in the name column? Then you might as well use group by name,info the result willbe the same.
But if the info column has different values within the same name, which one will you have displayed?
BTW: It should not be necessary to use distinct in this query
|||

Now i do realize I cant get the info field because its just a distinct query. Forget this message.
thanks anyway

Distinct on single column?

Hi,

This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.

SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d

INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p ON p.code = sol.part

WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0

ORDER BY (p.code), datum DESC

The problem with this is that it returns multiple lines for every product (p.code). Like so:

code description price quantity total date

603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00

603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00

603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00

603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00

603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00

603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00

603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00

I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved

edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.

Hello

Just a few ideas, do not have an MSSQL instace nearby to test:

1) use cursor, which I'd prefer to avoid

- declare cursor for "Select Distinct (code) From EfasLive..part"

- for each cursor value do the select on joined tables where date = MAx(date) to get the most recent value

2) do something like

Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)

The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)

3) try to use CTEs (Common Table Expressions)

Post the solution after you find one! Tnx
|||

Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me I'll most definitely look into CTEs and post my findings or a solution.

edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE Even then I'd have no clue how to drop the older records The only technique I know is to group them but then you'd have to use MAX or COUNT or AVG or whatever .. and then I wouldn't have the correct price and/or date. So you wouldn't realy be dropping them.

I'll look into the pointer thing.

|||

Here the query,

Code Snippet

;With CTE

as

(

SELECT DISTINCT

p.code

, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description

, sol.p_sales as price

, sol.q_ordered as quantity

, sol.p_sales * sol.q_ordered as total

, so.date_in as date

, max(so.date_in) over(partition by p.code) as maxdate

--, Row_Number() over(partition by p.code order by so.date_in desc) rid

FROM

EfasLive..debtor AS d

INNER JOIN Informatica..so AS so

ON so.deb_code = d.code

AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol

ON sol.code = so.code

AND sol.co_code = so.co_code

AND sol.acc_year = so.acc_year

AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p

ON p.code = sol.part

WHERE

d.[grp{003}] = 'GROUP'

AND p.co_code = 1

AND p.code NOT LIKE '&%'

AND so.date_in > DATEADD(m,-3,GETDATE())

AND sol.q_ordered > 0

)

Select

code

, description

, price

, quantity

, total

, date

From

CTE

Where

date = maxdate

--rid=1

Distinct in Select Statement

Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I'm searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I'm just planning ahead.

I'm programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.SELECT DISTINCT(CompanyName)
FROM Company
WHERE Product.State = stateVariable
AND Product.CompanyID = Company.CompanyID|||thanks, I'll give that a shot.

Display empty rows

I am creating some reports with crystal reports 8.5

I made crosstabs listing the sales of certain products by store and month.

The stores are listed in the rows, products and months are in the columns. In the complete yearly reports everything is fine, because there are no empty rows.

When i create subreports for just a certain month or product, there might be empty rows, like, there might have been no sales of that particular product, in that store in that month. Instead of just displaying a row with the store name and Zero value, it just doesn`t show the row at all.

I need those empty rows to show and display zero values. The option "suppress empty rows" is not checked.

Any ideas ?

Example
...............January 2007
...............Product 1
Store1......0 <- this row is not displayed
Store2......6
Store3......24You'll need an outer join somewhere, rather than an inner join - read the help on join types, or describe your current tables and joins.|||I am just beginning in crystal but I did have the same issue but my training yesterday was about table joints.
You have to have your primary table as your "source" table where you have most of your data. When you link your secondary table, go to link options and you need to have the linking as "left join or outter join ...not Equal". Even though you have no value on that one record, it will show it with an empty cell or box but you will see everything else from that record. I still wouldn't know how to display the zero.