Grouping records - HOW TO

Hi,
Is there a way that I can get the same sort of output as when you create a
relationship between two tables, but with only one table?
With a dataset containing two (or more) tables that has a data relation
added, you get a resultant output (say a datagrid) that groups by the column
detailed in the relationship. When the datagrid is displayed it shows each
parent record (row) with a '+' next to it. When selected you can then
display the related child records.
I have a single table with multiple records. Let's say one field is name.
There may be multiple records for each person who is displayed in the name
field. Instead of doing a simple sort and showing all records at the same
time, I would like to have the '+', and only show the persons name once. I
could then expand that record to show all for that person.
Is this possible?
I am using VB.Net, windows display (not IE), and sql server.
Hope there is an answer out there............even if it is NO ;-)
Rgds, PhilPhil,
use
SELECT DISTINCT name FROM table
for the first DataTable and
SELECT name, other_cols_needed FROM table
for the second DataTable.
Then add a DataRelation on the name column.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Phil" <Phil@.nospam.com> wrote in message
news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Hi,
> Is there a way that I can get the same sort of output as when you create a
> relationship between two tables, but with only one table?
> With a dataset containing two (or more) tables that has a data relation
> added, you get a resultant output (say a datagrid) that groups by the
> column detailed in the relationship. When the datagrid is displayed it
> shows each parent record (row) with a '+' next to it. When selected you
> can then display the related child records.
> I have a single table with multiple records. Let's say one field is name.
> There may be multiple records for each person who is displayed in the name
> field. Instead of doing a simple sort and showing all records at the same
> time, I would like to have the '+', and only show the persons name once. I
> could then expand that record to show all for that person.
> Is this possible?
> I am using VB.Net, windows display (not IE), and sql server.
> Hope there is an answer out there............even if it is NO ;-)
> Rgds, Phil
>|||Dejan,
Thanks for the response but I only have ONE table, as stated. If I had two
tables then it wouldn't be a problem for me. Can it be done with one table?
Cheers, Phil
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23r89FNBoFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Phil,
> use
> SELECT DISTINCT name FROM table
> for the first DataTable and
> SELECT name, other_cols_needed FROM table
> for the second DataTable.
> Then add a DataRelation on the name column.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Phil" <Phil@.nospam.com> wrote in message
> news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||> Thanks for the response but I only have ONE table, as stated. If I had two
> tables then it wouldn't be a problem for me. Can it be done with one
> table?
You can use two selects to fill two DataTable objects from a single SQL
table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Grouping record based on a condtion

TechnologyTypeSize

XYZA200

XYZ1A200

XYZ2A300

XYZ3A300

ABC1X238

ABC2X238

PQRB320

MNOC330

I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.

Now I need to concatenate the Technology based on Type and size.

As you can see in Type A we have two sizes 200 and 300.

We need to group the Technology of type A with same size together.

So the output of the procedure should be

XYZ + XYZ1

XYZ2+ XYZ3

ABC1 + ABC2etc.

We need to concatenate the Technology string with the next technology if they have the same type and size.

Can somebody please help or send any sample code.

Any help is greatly appreciated

Thanks

Swapna

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

|||

you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.

Thanks for your reply

Swapna

|||

and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size

TechnologyTypeSize

XYZA200

ABCA200

ABC1A300

XYZ3A300

MNO1X238

ABC2X238

PQRB320

MNOC330

so the output should be XYZ+ABC

ABC1+XYZ3

MNO1+ABC2.... I hope I am clear now.

Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality

Thanks

|||

Hello:

The "techTable" would be the name of your table which holds your data.

The CTE code I posted will work in a recursive fasion.

If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).

|||

--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)

--Enter the values suggested

--Run the following code

DECLARE @.Type CHAR(1)

DECLARE @.Size INT

DECLARE @.MyNewString CHAR(11)

DECLARE @.MyNewString2 VARCHAR(MAX)

SET @.MyNewString2 = ''

--Replace MyTable with your tablename

--Replace Technology, Type, Size with your field names

CREATE TABLE #Temp(MyNewString VARCHAR(MAX))

DECLARE c1 CURSOR FOR

SELECT mt.Type, mt.Size

FROM MyTable mt

OPEN c1

FETCH NEXT FROM c1

INTO @.Type, @.Size

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE c2 CURSOR FOR

SELECT Technology from MyTable Where size = @.Size and type = @.Type

OPEN c2

FETCH NEXT FROM c2

INTO @.MyNewString

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))

FETCH NEXT FROM c2

INTO @.MyNewString

END

CLOSE c2

DEALLOCATE c2

INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)

SET @.MyNewString2 = ''

FETCH NEXT FROM c1

INTO @.Type, @.Size

END

CLOSE c1

DEALLOCATE c1

SELECT * from #Temp

GROUP BY MyNewString

DROP TABLE #temp

|||

limno wrote:

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, max(case t2.seq when 1 then t1.Technology end)

+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Below working query uses pubs authors table and you can do the same based on your table schema.

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?

...and could you post some working code. I'm interested in this approach but getting errors.

Thanks,

Adamus

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

I unmarked this as the answer because the poster requested a cursor approach.|||

Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.

Anyway, here is a query that uses pubs authors table:

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

The query produces a comma-separated list of author ids for each state and city combination similar to the problem.

Grouping Problem

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')

Grouping output

I have a query that drives the generation of a table. The query is filtered
during output. If the filtering results in no rows being output for the
table, i'd like to put some verbage on the table footer indicating "No
Matching Records" or something similar.
Is there an easy way to do this that i'm missing?
Thanks!
BrianTake a look at NoRows table property.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:e8xqt8gqEHA.324@.TK2MSFTNGP11.phx.gbl...
> I have a query that drives the generation of a table. The query is
filtered
> during output. If the filtering results in no rows being output for the
> table, i'd like to put some verbage on the table footer indicating "No
> Matching Records" or something similar.
> Is there an easy way to do this that i'm missing?
> Thanks!
> Brian
>sql

Grouped by Using aliased names

Hi,

I have a select statement that gives me an output as follows:

Date Store Num Location
4-5-2007 0001 NY
4-5-2007 0002 NY
4-5-2007 0002 NY
4-4-2007 0003 PA
4-4-2007 0002 PA

The store num and location columns are derived like so:
LEFT(Table.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(Table.WholeField, 6), 2) AS 'Location'

The problem I am running into is that I have been tasked to write a select statement that sums up distinct values for Store Num and Location. The output should look something like this :

Date Store Num Location Num
4-5-2007 0002 NY 2

However, 'Store Num' and 'Location' comes from one field by design. I have written a select statement that uses the GROUP BY function to get the correct output. However, I am receiving an invalid column name error because I am using an aliased name.

Does anyone have any insight into the error or a possible workaround.

Thanks,
V.Don't use the alias in the Group By, use the expression instead.

GROUP BY LEFT(Table.WholeField, 4), RIGHT(LEFT(Table.WholeField, 6), 2)

or just bury the original query as a subquery, and then sum and group by.

The first version seems "cleaner" to me though|||Thanks for the quick response. The Grouping by for the expressions works.

However, I am still getting:

Date Store Num Location Num
4-5-2007 0002 NY 1
4-5-2007 0002 NY 1

However, I am looking for :

Date Store Num Location Num
4-5-2007 0002 NY 2

Shouldn't the group by statement work with a correct count(*) or do I have to issue counts for the two separate columns?|||Can you post the SQL statement?|||Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!|||I don't see why this wouldn't work

SELECT [DATE],
LEFT(#TMP.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(#TMP.WholeField, 6), 2) AS 'Location',
COUNT(*)
FROM #TMP
GROUP BY [DATE],
LEFT(#TMP.WholeField, 4),
RIGHT(LEFT(#TMP.WholeField, 6), 2)

That yields

Date Store Num Location Num
4-4-2007 0002 PA 1
4-4-2007 0003 PA 1
4-5-2007 0001 NY 1
4-5-2007 0002 NY 2|||I was using a convert function to take the timestamp field Date and convert it to a MM-DD-YYYY format. However, in the group by statement i was using just the Fieldname Date. When I added the convert function to the group by, it worked.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
rowOn 21 Dec 2005 13:50:36 -0800, Matt wrote:
>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.
>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().
> Need to achive output of names not after one another if they are same
> name
> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegroups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegroups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegroups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>|||Thanks Don.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
row
On 21 Dec 2005 13:50:36 -0800, Matt wrote:

>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.

>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().

> Need to achive output of names not after one another if they are same
> name

> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegro ups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>
|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?
|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegr oups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>
|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers
|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegr oups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>
|||Thanks Don.

Group or Not group

Select FN,LN
Group by FN from A
Gives me
Bob
Bob
Tom
Tom
Tom
Bill
Bill
Need to achive output of names not after one another if they are same
name
Bob
Tom
Bill
Bob
Tom
Bob
Bill
Order is not important as long as they dont repaeat same name for next
rowOn 21 Dec 2005 13:50:36 -0800, Matt wrote:

>Select FN,LN
>Group by FN from A
>Gives me
>Bob
>Bob
>Tom
>Tom
>Tom
>Bill
>Bill
Hi Matt,
You must have made a mistake in this post. That query can never result
in anything but an error message.

>Need to achive output of names not after one another if they are same
>name
>Bob
>Tom
>Bill
>Bob
>Tom
>Bob
>Bill
>Order is not important as long as they dont repaeat same name for next
>row
Not sure I understand the requirements, but it sounds like a task for
the front end.
If you really want to do this server-side, then please proviude better
specs. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Since you specify no ORDER BY, SQL Server may return data in any sequence.
To return data in a pseudo random order, you can include ORDER BY NEWID().

> Need to achive output of names not after one another if they are same
> name

> Order is not important as long as they dont repaeat same name for next
> row
Is random order acceptable? In order words, is it ok if 2 or more same
names are consecutive, if only by coincidence?
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135201836.056730.13200@.o13g2000cwo.googlegroups.com...
> Select FN,LN
> Group by FN from A
> Gives me
> Bob
> Bob
> Tom
> Tom
> Tom
> Bill
> Bill
> Need to achive output of names not after one another if they are same
> name
> Bob
> Tom
> Bill
> Bob
> Tom
> Bob
> Bill
> Order is not important as long as they dont repaeat same name for next
> row
>|||Pushing those recordsets to another system, system does not allow me to
push smilar account one another.
So i cant push right after another. I need to push different name.
Since there are no other Indicator to differenciate the records
Above query was an example
any idea?|||As Hugo requested, we really need DDL, a working query and sample to data to
help you out. Presuming that FN is the differentiator, what should be done
when it is impossible to order results to prevent consecutive duplicates?
Consider the following:
Tom
Bob
Tom
Tom
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135260352.114345.114900@.g47g2000cwa.googlegroups.com...
> Pushing those recordsets to another system, system does not allow me to
> push smilar account one another.
> So i cant push right after another. I need to push different name.
> Since there are no other Indicator to differenciate the records
> Above query was an example
> any idea?
>|||SQL is almost 300 line thats why could not posted there
This is one something like
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/03'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '12/30/02'
GROUP BY (ACCOUNT)
Gives me result set of
7321234
7321234
7324567
7324567
I need result set to be
7321234
7324567
so on ,, as long as they dont repeat one after another.
Cheers|||> SQL is almost 300 line thats why could not posted there
300 lines is reasonable. Please post.
I still don't understand your problem. The query you posted will eliminate
duplicate account numbers due to the UNION as illustrated by the example
below.
CREATE TABLE TEMP
(
ACCOUNT int NOT NULL,
CLS_DATE smalldatetime NOT NULL
CONSTRAINT PK_TEMP PRIMARY KEY
(
ACCOUNT,
CLS_DATE
)
)
INSERT INTO TEMP VALUES (7321234, '20021229')
INSERT INTO TEMP VALUES (7321234, '20021230')
INSERT INTO TEMP VALUES (7321234, '20021231')
INSERT INTO TEMP VALUES (7321234, '20031229')
INSERT INTO TEMP VALUES (7321234, '20031230')
INSERT INTO TEMP VALUES (7321234, '20031231')
INSERT INTO TEMP VALUES (7324567, '20021229')
INSERT INTO TEMP VALUES (7324567, '20021230')
INSERT INTO TEMP VALUES (7324567, '20021231')
INSERT INTO TEMP VALUES (7324567, '20031229')
INSERT INTO TEMP VALUES (7324567, '20031230')
INSERT INTO TEMP VALUES (7324567, '20031231')
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20031230'
GROUP BY (ACCOUNT)
UNION
SELECT ACCOUNT
FROM TEMP
WHERE CLS_DATE > '20021230'
GROUP BY (ACCOUNT)
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt" <metehanIT@.Hotmail.com> wrote in message
news:1135265718.603518.119100@.g47g2000cwa.googlegroups.com...
> SQL is almost 300 line thats why could not posted there
> This is one something like
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/03'
> GROUP BY (ACCOUNT)
> UNION
> SELECT ACCOUNT
> FROM TEMP
> WHERE CLS_DATE > '12/30/02'
> GROUP BY (ACCOUNT)
> Gives me result set of
> 7321234
> 7321234
> 7324567
> 7324567
> I need result set to be
> 7321234
> 7324567
> so on ,, as long as they dont repeat one after another.
> Cheers
>|||Thanks Don.

Group by Max Query

Could any one help me how the Query should look like
I have three tables: Asset_Table, Assignment_Table and User_Table
Output should be: Asset_Number from Asset_Table where Asset_Number = 123, Date from Assignment_Table = MAX Date and First Name from User_Table
Thnx
PatrickMaybe
select asset_number, firstname , (select max(date) from Assignment_table
where assignment_table.id = asset_table.id)
from assignment_Table inner join User_table on assignment_Table.key =user_table.key
where asset_number = 123
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Patrick" <patrick.thie@.nl.mci.com> wrote in message
news:57B2D2A0-9656-4700-9616-E7A60EF4BEC9@.microsoft.com...
> Could any one help me how the Query should look like!
> I have three tables: Asset_Table, Assignment_Table and User_Table.
> Output should be: Asset_Number from Asset_Table where Asset_Number = 123,
Date from Assignment_Table = MAX Date and First Name from User_Table.
> Thnx,
> Patrick

group by

GROUP BY Clause
Specifies the groups into which output rows are to be placed and, if
aggregate functions are included in the SELECT clause <select list>,
calculates a summary value for each group. When GROUP BY is specified,
either each column in any non-aggregate expression in the select list should
be included in the GROUP BY list, or the GROUP BY expression must match
exactly the select list expression.
I don't understand the implication of having to include all columns in any
non-aggregate expression in the select list.
For example (from the "Commerce" ASP.NET Starter Kit) :
CREATE Procedure CMRC_CustomerAlsoBought
(
@.ProductID int
)
As
/* We want to take the top 5 products contained in
the orders where someone has purchased the given Product */
SELECT TOP 5
CMRC_OrderDetails.ProductID,
CMRC_Products.ModelName,
SUM(CMRC_OrderDetails.Quantity) as TotalNum
FROM
CMRC_OrderDetails
INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
CMRC_Products.ProductID
WHERE OrderID IN
(
/* This inner query should retrieve all orders that have contained the
productID */
SELECT DISTINCT OrderID
FROM CMRC_OrderDetails
WHERE ProductID = @.ProductID
)
AND CMRC_OrderDetails.ProductID != @.ProductID
GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
ORDER BY TotalNum DESC
CREATE TABLE [dbo].[CMRC_OrderDetails] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMRC_Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_Products] ADD
CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[CMRC_Orders] (
[OrderID]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMRC_Products] ADD
CONSTRAINT [FK_Products_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[CMRC_Categories] (
[CategoryID]
)
GOHi John

> I don't understand the implication of having to include all columns in any
> non-aggregate expression in the select list.
>
It is a requirement to do this otherwise you will get the error message such
as
Server: Msg 8120, Level 16, State 1, Line 5
Column 'CMRC_OrderDetails.ProductID' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause.
In your example as ProductID is the primary key for CMRC_Products it will
make no difference as all combinations of ProductID and ModelName are unique
.
HTH
John
"John Grandy" wrote:

> GROUP BY Clause
> Specifies the groups into which output rows are to be placed and, if
> aggregate functions are included in the SELECT clause <select list>,
> calculates a summary value for each group. When GROUP BY is specified,
> either each column in any non-aggregate expression in the select list shou
ld
> be included in the GROUP BY list, or the GROUP BY expression must match
> exactly the select list expression.
>
> I don't understand the implication of having to include all columns in any
> non-aggregate expression in the select list.
> For example (from the "Commerce" ASP.NET Starter Kit) :
>
> CREATE Procedure CMRC_CustomerAlsoBought
> (
> @.ProductID int
> )
> As
> /* We want to take the top 5 products contained in
> the orders where someone has purchased the given Product */
> SELECT TOP 5
> CMRC_OrderDetails.ProductID,
> CMRC_Products.ModelName,
> SUM(CMRC_OrderDetails.Quantity) as TotalNum
> FROM
> CMRC_OrderDetails
> INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
> CMRC_Products.ProductID
> WHERE OrderID IN
> (
> /* This inner query should retrieve all orders that have contained the
> productID */
> SELECT DISTINCT OrderID
> FROM CMRC_OrderDetails
> WHERE ProductID = @.ProductID
> )
> AND CMRC_OrderDetails.ProductID != @.ProductID
> GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
> ORDER BY TotalNum DESC
>
> CREATE TABLE [dbo].[CMRC_OrderDetails] (
> [OrderID] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [Quantity] [int] NOT NULL ,
> [UnitCost] [money] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CMRC_Products] (
> [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UnitCost] [money] NOT NULL ,
> [Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
> (
> [OrderID],
> [ProductID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
> (
> [ProductID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
> (
> [OrderID]
> ) REFERENCES [dbo].[CMRC_Orders] (
> [OrderID]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [FK_Products_Categories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[CMRC_Categories] (
> [CategoryID]
> )
> GO
>
>
>
>
>
>|||John,
A grouped query will not make sense if you don't follow this rule.
If you say
GROUP BY ProductID
you will retrieve one result row per ProductID. Suppose the table
you are querying contains 20 rows for ProductID number 123, and
additional columns Quantity and OrderID. If you ask for
select ProductID, OrderID, sum(Quantity)
group by ProductID
what do you want the query processor to do with the OrderID values
from the 20 ProductID=123 rows? The Quantity column will be summed,
but you can't return one result row (as the grouping requests) with 20
OrderID values - you either need to provide an aggregate to use on
the OrderID column or you need to change your mind and accept 20
rows in the result set by adding OrderID to the group by list.
Steve Kass
Drew University
John Grandy wrote:

>GROUP BY Clause
>Specifies the groups into which output rows are to be placed and, if
>aggregate functions are included in the SELECT clause <select list>,
>calculates a summary value for each group. When GROUP BY is specified,
>either each column in any non-aggregate expression in the select list shoul
d
>be included in the GROUP BY list, or the GROUP BY expression must match
>exactly the select list expression.
>
>I don't understand the implication of having to include all columns in any
>non-aggregate expression in the select list.
>For example (from the "Commerce" ASP.NET Starter Kit) :
>
>CREATE Procedure CMRC_CustomerAlsoBought
>(
> @.ProductID int
> )
>As
>/* We want to take the top 5 products contained in
> the orders where someone has purchased the given Product */
>SELECT TOP 5
> CMRC_OrderDetails.ProductID,
> CMRC_Products.ModelName,
> SUM(CMRC_OrderDetails.Quantity) as TotalNum
>FROM
> CMRC_OrderDetails
> INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID =
>CMRC_Products.ProductID
>WHERE OrderID IN
>(
> /* This inner query should retrieve all orders that have contained the
>productID */
> SELECT DISTINCT OrderID
> FROM CMRC_OrderDetails
> WHERE ProductID = @.ProductID
> )
>AND CMRC_OrderDetails.ProductID != @.ProductID
>GROUP BY CMRC_OrderDetails.ProductID, CMRC_Products.ModelName
>ORDER BY TotalNum DESC
>
>CREATE TABLE [dbo].[CMRC_OrderDetails] (
> [OrderID] [int] NOT NULL ,
> [ProductID] [int] NOT NULL ,
> [Quantity] [int] NOT NULL ,
> [UnitCost] [money] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[CMRC_Products] (
> [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ModelName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProductImage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UnitCost] [money] NOT NULL ,
> [Description] [nvarchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [PK_CMRC_OrderDetails] PRIMARY KEY NONCLUSTERED
> (
> [OrderID],
> [ProductID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [PK_CMRC_Products] PRIMARY KEY NONCLUSTERED
> (
> [ProductID]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[CMRC_OrderDetails] ADD
> CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
> (
> [OrderID]
> ) REFERENCES [dbo].[CMRC_Orders] (
> [OrderID]
> ) NOT FOR REPLICATION
>GO
>ALTER TABLE [dbo].[CMRC_Products] ADD
> CONSTRAINT [FK_Products_Categories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[CMRC_Categories] (
> [CategoryID]
> )
>GO
>
>
>
>
>
>
>|||I have posted a detailed description of how a SELECT statement works.
Look it up and you can see why your mental model is wrong.|||Hi Steve, and thanks for the response.
So, when specifying a GROUP BY clause, the only variable is the order in
which you list the column names. Every GROUP BY clause must provide the
query processor with instructions regarding how to order the rows in the
subgroups of any group. An therefore every non-aggregate column must be
included.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23v3PIq8KFHA.3064@.TK2MSFTNGP12.phx.gbl...
> John,
> A grouped query will not make sense if you don't follow this rule.
> If you say
> GROUP BY ProductID
> you will retrieve one result row per ProductID. Suppose the table
> you are querying contains 20 rows for ProductID number 123, and
> additional columns Quantity and OrderID. If you ask for
> select ProductID, OrderID, sum(Quantity)
> group by ProductID
> what do you want the query processor to do with the OrderID values
> from the 20 ProductID=123 rows? The Quantity column will be summed,
> but you can't return one result row (as the grouping requests) with 20
> OrderID values - you either need to provide an aggregate to use on
> the OrderID column or you need to change your mind and accept 20
> rows in the result set by adding OrderID to the group by list.
> Steve Kass
> Drew University
>
> John Grandy wrote:
>|||Hi Joe, and thanks for the response.
Where is your description ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111164541.019670.34390@.o13g2000cwo.googlegroups.com...
>I have posted a detailed description of how a SELECT statement works.
> Look it up and you can see why your mental model is wrong.
>|||
John Grandy wrote:

>Hi Steve, and thanks for the response.
>So, when specifying a GROUP BY clause, the only variable is the order in
>which you list the column names. Every GROUP BY clause must provide the
>query processor with instructions regarding how to order the rows in the
>subgroups of any group. An therefore every non-aggregate column must be
>included.
>
I think you've got it, but just to make sure: there's nothing in a GROUP
BY query
that has anything to do with "how to order the rows", though that might
be your
interpretation of how a MIN or MAX aggregate is evaluated. There are other
aggregates, though, like AVG and COUNT, that don't correspond to the first
or last value in some order, as MIN and MAX do.
While changing the order of the column names in the GROUP BY clause
might change the order in which your results appear, think of any such
behavior as coincidental. If you want the result rows in a particular
order,
use an ORDER BY clause.
What the query processor needs to know in a grouping query is what
to do with the table source (those rows specified by what's in the FROM
and WHERE clauses. For each distinct combination of values of the
columns mentioned in the SELECT clause and in the group by clause,
you'll see one row in the result set. This row may correspond to one or
many rows in the table source, depending on how many times the
distinct combination appears. The additional columns of the result
set must all be aggregates, and each will represent a min, max,
avg, count, etc., for the one group of rows the result row corresponds
to.
So grouping queries give you one result row per group. The GROUP
BY clause identifies the columns used to specify each group, and the
remaining result columns are aggregate values for those groups.
SK

>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23v3PIq8KFHA.3064@.TK2MSFTNGP12.phx.gbl...
>
>
>|||You can find it here:
[url]http://groups.google.nl/groups?hl=nl&lr=&q=Here+is+how+a+SELECT+works+in+SQL+...+a
t+least+in+theory&btnG=Zoeken&meta=group%3Dmicrosoft.public.sqlserver.programming[
/url]
(url may wrap)
HTH,
Gert-Jan
John Grandy wrote:
> Hi Joe, and thanks for the response.
> Where is your description ?
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1111164541.019670.34390@.o13g2000cwo.googlegroups.com...|||Great explanation, Steve !
"Steve Kass" <skass@.drew.edu> wrote in message
news:eVVf4UCLFHA.244@.TK2MSFTNGP12.phx.gbl...
>
> John Grandy wrote:
>
> I think you've got it, but just to make sure: there's nothing in a GROUP
> BY query
> that has anything to do with "how to order the rows", though that might be
> your
> interpretation of how a MIN or MAX aggregate is evaluated. There are
> other
> aggregates, though, like AVG and COUNT, that don't correspond to the first
> or last value in some order, as MIN and MAX do.
> While changing the order of the column names in the GROUP BY clause
> might change the order in which your results appear, think of any such
> behavior as coincidental. If you want the result rows in a particular
> order,
> use an ORDER BY clause.
> What the query processor needs to know in a grouping query is what
> to do with the table source (those rows specified by what's in the FROM
> and WHERE clauses. For each distinct combination of values of the
> columns mentioned in the SELECT clause and in the group by clause,
> you'll see one row in the result set. This row may correspond to one or
> many rows in the table source, depending on how many times the
> distinct combination appears. The additional columns of the result
> set must all be aggregates, and each will represent a min, max,
> avg, count, etc., for the one group of rows the result row corresponds
> to.
> So grouping queries give you one result row per group. The GROUP
> BY clause identifies the columns used to specify each group, and the
> remaining result columns are aggregate values for those groups.
> SK
>

Grid vs Text output

In SQL Query Analyzer, there is a Query drop down window that gives you
the option to change the output from grid to text for printing if
needed. My question is, can this be programmed so a stored procedure
will always print in text without having to manually change the window
each time the procedure is run? I could find nothing under the logical
searches in books online.

Thanks JABYou can modify the QA to use text or use a grid regardless what you are
running in the QA. You can't set default behavior for each object or
each type of object.

Adi
jab wrote:

Quote:

Originally Posted by

In SQL Query Analyzer, there is a Query drop down window that gives you
the option to change the output from grid to text for printing if
needed. My question is, can this be programmed so a stored procedure
will always print in text without having to manually change the window
each time the procedure is run? I could find nothing under the logical
searches in books online.
>
Thanks JAB